Problem: I am trying to insert a .csv file with thousands/millions of rows into a SQL Server table with a bulk insert query. Now I want to update the table with bulk update where specific column is change e.g where price is changed. How can I do this? And also: I want to ignore constraint when inserting into the table
问题：我试图将带有数千/数百万行的.csv文件插入带有批量插入查询的SQL Server表中。现在我想用批量更新来更新表，其中特定列是更改的，例如价格在哪里更改。我怎样才能做到这一点？而且：我想在插入表格时忽略约束
BULK INSERT Table
FROM 'D:\test.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO
Now e.g table contains price column when second time I update the file only that row update which has different price before
现在例如表格包含价格列，当我第二次更新文件时，只有那个具有不同价格的行更新
2 个解决方案
#1
3
Do it in two (or more) steps.
在两个（或更多）步骤中完成。
Insert the raw data (flaws and all), and then run a separate update statement to make it look how you want.
插入原始数据（缺陷和所有），然后运行单独的更新语句，使其看起来如您所愿。
For a large or busy table, or in order to keep the new data separate until it's ready, you may also want to first bulk insert into a separate holding table, massage and clean the data there, and then migrate from the holding table to the final table.
对于大型或繁忙的桌子，或者为了使新数据保持分离直到准备就绪，您可能还需要首先批量插入到单独的保持表中，按摩并清理那里的数据，然后从保持表迁移到决赛桌。
Alternatively you can write a client program to pre-clean the data before the bulk insert, or you can use a tool like Sql Server Integration Services (SSIS) to handle the import. SSIS has a lot of nice features for handling this kind of thing.
或者，您可以编写客户端程序以在批量插入之前预清理数据，或者您可以使用Sql Server Integration Services（SSIS）之类的工具来处理导入。 SSIS有很多很好的功能来处理这种事情。
What you won't be able to do is make a simple or quick adjustment to the bulk insert code. It does what it does, and nothing more.
您无法做的是对批量插入代码进行简单或快速的调整。它做了它的功能，仅此而已。
#2
1
You cannot just bulk upload one file on top of a previous upload and only record the differences. You either completely refresh the data (ie: a full load that overwrites), upload the full CSV into a staging table and compare the two tables using SQL code or you can use a tool such as SSIS to connect to the CSV file, check it against the vales in your table and trigger the updates from there.
您不能只是在上一次上传之前批量上传一个文件，而只记录差异。您要么完全刷新数据（即：覆盖的完整加载），将完整的CSV上传到临时表并使用SQL代码比较两个表，或者您可以使用SSIS等工具连接到CSV文件，检查它对着表中的vales并从那里触发更新。
#1
3
Do it in two (or more) steps.
在两个（或更多）步骤中完成。
Insert the raw data (flaws and all), and then run a separate update statement to make it look how you want.
插入原始数据（缺陷和所有），然后运行单独的更新语句，使其看起来如您所愿。
For a large or busy table, or in order to keep the new data separate until it's ready, you may also want to first bulk insert into a separate holding table, massage and clean the data there, and then migrate from the holding table to the final table.
对于大型或繁忙的桌子，或者为了使新数据保持分离直到准备就绪，您可能还需要首先批量插入到单独的保持表中，按摩并清理那里的数据，然后从保持表迁移到决赛桌。
Alternatively you can write a client program to pre-clean the data before the bulk insert, or you can use a tool like Sql Server Integration Services (SSIS) to handle the import. SSIS has a lot of nice features for handling this kind of thing.
或者，您可以编写客户端程序以在批量插入之前预清理数据，或者您可以使用Sql Server Integration Services（SSIS）之类的工具来处理导入。 SSIS有很多很好的功能来处理这种事情。
What you won't be able to do is make a simple or quick adjustment to the bulk insert code. It does what it does, and nothing more.
您无法做的是对批量插入代码进行简单或快速的调整。它做了它的功能，仅此而已。
#2
1
You cannot just bulk upload one file on top of a previous upload and only record the differences. You either completely refresh the data (ie: a full load that overwrites), upload the full CSV into a staging table and compare the two tables using SQL code or you can use a tool such as SSIS to connect to the CSV file, check it against the vales in your table and trigger the updates from there.
您不能只是在上一次上传之前批量上传一个文件，而只记录差异。您要么完全刷新数据（即：覆盖的完整加载），将完整的CSV上传到临时表并使用SQL代码比较两个表，或者您可以使用SSIS等工具连接到CSV文件，检查它对着表中的vales并从那里触发更新。