sqlbulkcopy, i want to overwrite current rows in the database

Question

In my program I am populating a datatable and then bulkcopying it into my database.

However, I want it to overwrite a row in the database if 3 of the columns in the the row match.

For example if I have 5 columns

membernum dateregistered name address city state

So if the membernum, dateregistered and name match I want to overwrite the row. with the data from the bulk insert. In my program the entire database table is not populated and I think its to big to load it.

Using bcp As SqlBulkCopy = New SqlBulkCopy(SqlDataSource2.ConnectionString)

    bcp.DestinationTableName = "dbo.dashboardtasks"

    bcp.WriteToServer(table)

If the best method is to create a datatable with my database table how would I compare the rows and then overwrite the tables where there are matches in the particular 3 columns. and then update the database with only the rows that have either changed or the new ones

INSERT INTO [dashboardtasks] ([tour], [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod], [id])

Answer 1

At Mitch points out the bulk copy functions (SqlBulkCopy, bcp and BULK INSERT) only handle inserts, not updates.

The normal approach to your problem is to perform the bulk load in two (or more) steps - first you use bulk inserts to load your data into a staging table, and then use a query to insert / update records in the main tables based on the data in the staging tables, for example:

INSERT INTO MyTable
(Column1, Column2, [etc...])
SELECT Column1, Column2, [etc...] 
FROM Test_Staging
WHERE [Some condition]

See SQL SERVER – Insert Data From One Table to Another Table for information on how to insert into one table from another table - it is also possible to do a join in an UPDATE, but I'm struggling to find a good resource on this.