假設我們有一個文字檔, 要寫入 table: StList 中, 且 StList schema 如下:

CREATE TABLE StList
(
 StFName varchar(50) NOT NULL,
 StLName varchar(50) NOT NULL,
 StEmail varchar(100) NOT NULL
)
go

 

文字檔型態一:

Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com

這個例子較簡單, 因此我們直接用下述語法:

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = ',')

 

文字檔型態二:

"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","Parker"

這個例子就比較麻煩了, 若直接用以下語法,

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = '","')

因為每個欄位的資料前後都有 double quote, 若直接用 "," 分開, 將造成"最前面"和"最後面"的double quote 拿不掉, 也就是會變成:

"Kelly Reynold kelly@reynold.com"
"John Smith bill@smith.com"
"Sara Parker Parker"

可改用以下程式處理:

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = '","')

UPDATE StList SET StFName = SUBSTRING(StFName,2,DATALENGTH(StFName)-1)    -- "KellyKelly

UPDATE StList SET StEmail = SUBSTRING(StFName,1,DATALENGTH(StEmail )-1)    -- kelly@reynold.com"kelly@reynold.com

 

 

創作者介紹

A little IT experience/study/share garden

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()