[SQL][問題處理]利用 View 來解決 bulk insert 時，克服DB欄位與csv檔中欄位不對等的問題
早上在 Super SQL Server 的 FB 社群中看到一個蠻好玩的問題，有朋友在詢問 "請問當使用bulk insert塞資料時，有辦法克服DB欄位與csv檔中欄位不對等的問題嗎？"，其實這個問題當使用 BCP 或者是 Bulk Insert 都會遇到，因為他們都需要一對一的欄位。而以往看到很多的朋友在處理的時候，有的人就捨棄不使用，有的人是自己利用寫程式去處理，這裡我提供一個自己常用的方式，利用 View 來騙過去。
首先我先建立一個範例用的 Items 表格
SET NOCOUNT ON IF OBJECT_ID('Items') IS NOT NULL DROP TABLE Items DECLARE @Items TABLE ( Item_Id varchar(10), Item_Name varchar(10), Sales_Qty numeric(10) ) DECLARE @Ptr INT, @RecordCount INT SET @Ptr = 0 SET @RecordCount = 5000 -- 範例表的筆數 -- 亂數產生資料 WHILE @Ptr < @RecordCount BEGIN SET @Ptr += 1 INSERT INTO @Items VALUES ( RIGHT('000000000'+LTRIM(STR(@Ptr)),10 ), RIGHT(newid(),10 ) , ROUND( RAND()*100,1 ) ) END -- 產生測試表格 Items SELECT * INTO Items FROM @Items
此時我們可以先用指令將資料匯出成為 BCP 檔案
bcp Source.dbo.Items in Items.bcp -T -n
此時我們利用 TRUNCATE TABLE 的指令刪除表格內的資料，並且調整一下 Items 的欄位，增加兩個測試的欄位在當中，這兩個增加的欄位我們故意不放在最後面，這樣比較能貼近我們的真實狀況。
再利用 BCP 的指令匯回剛剛產生的資料，果然會發生錯誤的訊息 ( 預設的最大錯誤數是 10 )
此時我們利用 CREATE VIEW 的指令來建立一個用來做匯入的對應表格
CREATE VIEW [dbo].[Mock_Items] AS SELECT Item_Id,Item_Name,Sales_Qty FROM Items
此時我們調整一下匯入的指令，改成匯入到 Mock_Items 這個 View，再測試一次，果然如我們預期的將資料給匯入成功了。
查看一下資料真的如我們原始資料一樣，沒有對應的欄位這個時候欄位就會是 NULL，但如果不想要 NULL 的話，則可以調整欄位的 DEFAULT 值
這裡我們將欄位 XXX 預設值設定為 0 , 欄位 YYY 預設是今天的日期。
ALTER TABLE dbo.Items ADD CONSTRAINT DF_Items_XXX DEFAULT (0) FOR XXX GO ALTER TABLE dbo.Items ADD CONSTRAINT DF_Items_YYY DEFAULT (getdate()) FOR YYY GO
接下來我們再將 Items 這個資料表給清空，再重新匯入一次，此時每個欄位就會有值了
同樣的方式，要是我們的資料來源是 CSV 檔案，則也可以用同樣的方式
BULK INSERT Mock_Items FROM 'D:\Sample.csv' WITH ( FIELDTERMINATOR =',' )
這樣也是可以成功的瞞天過海，把資料按照我們想要的方式給匯入了。