(BULK INSERT)文本导入SQL,字段数量不等.

要把文本数据导入到数据库，本文只有3个字段，数据库表有7个字段，
怎么把文本字段的对应到表的字段，如何用bulk insert来实现?

数据库表
userinfo
id identity，userName，pass，address，phone，email，registerTime

文本格式是
userName，address，phone
hua，湖南，5971898

--SQL2005处理方式:

先在G盘存放一个格式化文件

G:\format.xml

XML code :

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> <? 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 >

SQL code :

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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 行受影响) */

--SQL2000处理方式:

先在G盘存放一个格式化文件

G:\format.fmt

FMT code :

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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 ""

SQL code

<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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://topic.youkuaiyun.com/u/20090913/15/fa2e7e65-73d8-4b64-b6e0-bd583f564d86.html?95717