CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
Henrik
Henrik

Reputation: 1807

Bulk insert into existing table - only insert new rows

I've got a method to get data from existing table in database as DataTable and i want to add new rows to that datatable and then use SQLBulkCopy to only bulk insert the newly added rows and not the already existing rows, do i need to configure this somehow or will it only add the new rows anyway?

var myDataTable = GetExistingTableDataAsDataTable();

//add new rows to myDataTable.....

using (var conn = new SqlConnection("Connection string"))
    {
        conn.Open();

        using (var bulk = new SqlBulkCopy(conn))
        {
            bulk.DestinationTableName = myDataTable.TableName;
            bulk.BatchSize = 1000;

            foreach (var column in myDataTable.Columns)
            {
                bulk.ColumnMappings.Add(column.ToString(), column.ToString());   
            }

            bulk.WriteToServer(myDataTable); //Do i need to add DataRowState.Added here?
            bulk.Close();
        }

        conn.Close();
    }

Upvotes: 1

Views: 2490

Answers (1)

Andomar
Andomar

Reputation: 238076

Use a temporary table to bulk insert. Then, run a query to copy new rows over, filtering out duplicates with a where clause:

insert  YourTable
        (id, col1, col2, col3, ...)
select  id
,       col1
,       col2
,       col3
,       ...
from    TempTable t
where   not exists
        (
        select  *
        from    YourTable yt
        where   yt.id = t.id
        )

Upvotes: 1

Related Questions