第一种方法:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
G:\format.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="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="userName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="address" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="phone" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
G:\test.txt
userName,address,phone
hua,湖南,5971898
--SQL SERVER
--创建表
CREATE TABLE userinfo(id INT identity,userName varchar(20),
pass varchar(20),address varchar(100),phone varchar(20),
email varchar(128),registerTime datetime)
--导入
INSERT INTO userinfo(userName,address,phone)
SELECT * FROM OPENROWSET(BULK 'G:\test.txt',FORMATFILE='G:\format.xml',FIRSTROW=2) AS T;
--查看数据
SELECT * FROM userinfo;
/*
id userName pass address phone email registerTime
----------- --------- ---------- ----------- ---------- ---------- -------------
1 hua NULL 湖南 5971898 NULL NULL
(1 行受影响)
*/
------------------------------------------------
备注:
SQL 2000中不可以用xml格式化文件.
只能用非xml格式化文件.
非xml格式化文件比xml格式化文件的可读性差很多
------------------------------------------------------------------
第二种方法:
-----------------------------------------------------------------------------------------------------
G:\format.fmt
8.0
3
1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS
2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "\r\n" 5 phone ""G:\test.txt--------------------------------------
userName,address,phone
hua,湖南,5971898
--SQL SERVER
--建表
CREATE TABLE userinfo(id int identity,userName varchar(20),
pass varchar(20),address varchar(100),phone varchar(20),
email varchar(128),registerTime datetime)
--导入
BULK INSERT userinfo
FROM 'G:\test.txt'
WITH
(
FORMATFILE = 'G:\format.fmt',
FIRSTROW = 2
)
--查看数据
SELECT * FROM userinfo;
/*
id userName pass address phone email registerTime
----------- --------- ---------- ----------- ---------- ---------- -------------
1 hua NULL 湖南 5971898 NULL NULL
(1 行受影响)
*/8.0 --这个8,表示版本是8.0
3 --这个3.表示数据文件中有几列.
第一列的1,2,3表示数据文件的列的序号
而后面的第六列的2,4,5表示userName,Address,phone在表中.是第几列
1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS
2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "\r\n" 5 phone ""
原文详见:
http://bbs.csdn.net/topics/320027629
参考资料:
https://technet.microsoft.com/zh-cn/library/ms188365(SQL.90).aspx
https://technet.microsoft.com/zh-cn/library/ms191485(SQL.90).aspx
https://msdn.microsoft.com/zh-cn/library/ms178129.aspx