sqlbulkcopy，我想覆盖数据库中的当前行 [英] sqlbulkcopy, i want to overwrite current rows in the database

问题描述

在我的程序中，我正在填充一个数据表，然后将其批量复制到我的数据库中.

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

但是，如果行中的 3 列匹配，我希望它覆盖数据库中的一行.

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

例如，如果我有 5 列

For example if I have 5 columns

会员号注册日期姓名地址城市状态

membernum dateregistered name address city state

因此，如果 membernum、dateregistered 和 name 匹配，我想覆盖该行.与来自批量插入的数据.在我的程序中，没有填充整个数据库表，我认为加载它太大了.

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)

如果最好的方法是用我的数据库表创建一个数据表，我将如何比较行，然后覆盖特定 3 列中有匹配项的表.然后仅使用已更改的行或新的行更新数据库

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])

推荐答案

At Mitch 指出了批量复制功能 (SqlBulkCopy，bcp 和 BULK INSERT) 只处理插入，不处理更新.

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]

见SQL SERVER – 将数据从一个表插入到另一个表 有关如何从另一个表插入一个表的信息 - 也可以进行连接在 UPDATE 中，但我正在努力寻找这方面的好资源.

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.

这篇关于sqlbulkcopy，我想覆盖数据库中的当前行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！