Tsql 幫助 - 從 CSV 文件到 ssms 中的表進行大量更新

我得到了一個 csv 文件，其中只有 id，這些 id 位於 ssms 中的表中，其中列需要更新。在該表中的 90,000 個 id 中，只有 83,000 個需要從 csv 文件更新。

主表是：

ID  ENABLED    AGREED
1       1                       no  
2       1                       no
3       1                       no
4       0                       yes

我知道我可以根據我在 csv 中給出的 id 對錶格進行更新，例如：

update table1
set enabled = 0,
agreed = 'yes',
where id in('1','2','3')

但是，我的問題是，其中有 83,000 個需要更新到這些特定條件，並希望找到最好的方法來做到這一點。我被告知要編寫一個腳本將 CSV 導入臨時表，然後將該臨時表加入主表並執行更新，而不是在腳本中硬編碼一個 id 列表。

我怎樣才能做到這一點？有誰知道更好的方法？

這篇文章有一些可靠的答案，但作為一個額外的補充，如果你知道這將是一項正常任務，並且每週都會以相同的格式和名稱將文件拖放到特定位置，可以使用 OPENROWSET 功能通過 SQL 直接查詢 csv、txt 等平面文件，然後使用插入和更新對數據進行操作。（批量插入也是一個選項，可以更簡單，但在這種情況下，我選擇 OpenRowSet 是為了便於更新。）

這需要一些設置，但是一旦你開始工作，你可以創建一個視圖來讀取具有固定輸出的 CSV，或者甚至使用儲存過程來更新你的表，這樣你就可以自動化你的吞吐量，你可以甚至將您的 CSV 加入現有數據，儘管它不會被索引，因此您可能需要考慮使用該功能將數據暫存到表中，索引該表，然後執行更新。

雖然，如果您要進行適當的吞吐量，您應該查看 SSIS，因為您可以在吞吐量和輸出位置中為錯誤輸入設置正確的管道錯誤。

我將對你的數據做一些假設。

• 您正在使用製表符空格來分隔它們
• 您使用 CRLF 作為輸入符。
• 我假設您在第二列中使用 INT 而不是 BIT。

因此，SQL 有一個更寬鬆的系統不需要的弱點，但該弱點使其具有的所有優勢成為可能。它需要列的定義。未明確包含在 CSV 文件中的定義。因此，您需要在 XML 文件中創建定義並將 SQL 指向它們，您可以在定義性 XML 中執行此操作，在本例中使用 BCPFORMAT。

定義 XML 文件將被稱為：BCP_FileName.XML

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
 <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='\t' />
 <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='\t' />
 <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='\r\n' />
</RECORD>
<ROW>
 <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT" />
 <COLUMN SOURCE="2" NAME="Enabled" xsi:type="SQLINT" />
 <COLUMN SOURCE="3" NAME="Agreed" xsi:type="SQLVARYCHAR" />
</ROW>
</BCPFORMAT>

CSV 將被稱為Load.C​​SV

最終的選擇語句可能是。

 SELECT ID,Enabled,Agreed
 FROM OPENROWSET(BULK 'C:\Location\Load.CSV',
 FIRSTROW = 2,
 FORMATFILE='C:\Location\BCP_FileName.XML'     
 ) as t1

這將使您能夠使用 create view 語句將其放入視圖中

Create View dbo.CSVQuery
as
SELECT ID,Enabled,Agreed
 FROM OPENROWSET(BULK 'C:\Location\Load.CSV',
 FIRSTROW = 2,
 FORMATFILE='C:\Location\BCP_FileName.XML'     
 ) as t1

有了這個觀點，你可以做…

UPDATE st
SET Enabled = cq.Enabled,
agreed = cq.Agreed
FROM SourceTable st
INNER JOIN dbo.CSVQuery cq ON cq.ID = st.ID

這樣做的好處是能夠查詢視圖，以便您可以感知檢查數據和所有類型。

當然，如果您定期暫存重要數據，您可能希望選擇 SSIS，因為上述導入數據的方式可以是全部或全部，但如果這符合您的目的，那麼它可能會起作用。

您應該得到的一件事是 Notepad++，這樣您就可以準確地檢查隱藏字元的 CRLF，因為某些 CSV 文件使用它自己的 LF 或 CR。

注意：如果您打算將其用作正常載入過程，則必須授予 SQL 實例訪問文件位置的權限。為此，請進入您的伺服器，選擇服務，檢查哪個使用者正在執行該程序（如果文件位置不在同一伺服器上，請確保它是經過域驗證的帳戶，以便您可以利用域使用者訪問該位置用於文件拾取。）

注意 2：您應該知道，此過程將更新內容，因此惡意攻擊完全有可能真正造成一些損害，因此請確保將輸入過程限制為受信任的使用者。

這是 XSI 類型的資源： https ://docs.microsoft.com/en-us/openspecs/sql_data_portability/ms-bcp/51298f0a-c9ac-463a-8e01-76d25ebaca3c

這是 OpenRowSet 概述。 https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15

引用自：https://dba.stackexchange.com/questions/290626