close
假設我們有一個文字檔, 要寫入 table: StList 中, 且 StList schema 如下:
CREATE TABLE StList
(
StFName varchar(50) NOT NULL,
StLName varchar(50) NOT NULL,
StEmail varchar(100) NOT NULL
)
go
(
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
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"
"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) -- 【"Kelly】 變 【Kelly】
UPDATE StList SET StEmail = SUBSTRING(StFName,1,DATALENGTH(StEmail )-1) -- 【kelly@reynold.com"】 變 【kelly@reynold.com】
全站熱搜
留言列表
To be a MIS (2)