34,591
社区成员
发帖
与我相关
我的任务
分享
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 ""
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 行受影响)
*/
<?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 行受影响)
*/
批量导入用这个:
请参考:
1:不带有条件的从文本导入到数据库:
EXEC master..xp_cmdshell 'BCP bank..sss in E:\实验用数据库\ddd.txt -c -S7824558CF32B4F1\SQL01 -Usa -P520520'
2:不带有条件的从数据库导出到文本:
EXEC master..xp_cmdshell 'BCP bank..bank out E:\实验用数据库\aaa.txt -c -S7824558CF32B4F1\SQL01 -Usa -P520520'
3:带有条件的从数据库导出(转换成本)
EXEC master..xp_cmdshell 'BCP "select * from bank..sss where conut<10 " queryout E:\实验用数据库\sss.txt -c -S7824558CF32B4F1\SQL01 -Usa -P520520'