SQL Server大資料插入方法比較多,下面我們先談談Bulk insert 方法。
首先,我們建立一張TABLE,如下面T-SQL腳本:
create table TestTable2
(id int,
amount int check(amount >=1000 and amount<=5000))
假設有這樣的文本資料 testdata.txt:
1 700
2 2000
3 870
4 4500
下面這個語句不檢查限制:
1: bulk insert TestTable2
2: from 'D:\testdata\testdata.txt'
3: with
4: (fieldterminator=',',
5: rowterminator='\n')
這個是啟用限制的:
1: bulk insert TestTable2
2: from 'D:\testdata\testdata.txt'
4: (fieldterminator=',',
5: rowterminator='\n',
6: check_constraints)
7: select * from test
還可以使用FIRSTROW和LASTROW限制行數。如下COPY前三行:
1: bulk insert TestTable2
2: from 'D:\testdata\testdata.txt'
3: with
4: (fieldterminator=',',
5: rowterminator='\n',
6: FIRSTROW =1,
7: LASTROW=3)
使用ERRORFILE選項 錯誤處理,如下記錄到d:\error.txt
1: bulk insert test
2: from 'D:\testdata\testdata.txt'
3: with
4: (fieldterminator=',',
5: rowterminator='\',
6: FIRSTROW =1,
7: LASTROW=3,
8: ERRORFILE ='F:\error.txt',
9: check_constraints)