从MSDN上看到实现大容量导入数据时保留标识值得方法包含三种：
MSDN链接地址为：https://msdn.microsoft.com/zh-cn/library/ms178129.aspx
感觉MSDN上给的列子都没有数据，有些demo不直接，所以这里我要写例子来实现这三种方式。
- bcp
- Bulk Insert From .. With(...)
- Insert Into ... (field1name,field2name...) select field1name,field2name... from openrowset(bulk 'xxx',formatfile='xxx')
下边我们就三种方式展开测试：
整理数据源:
1 create table dbo.Member( 2 id bigint identity(1,1) primary key not null, 3 name nvarchar(64) not null, 4 nickname nvarchar(64) null, 5 pwd nvarchar(64) not null, 6 moneyicon decimal(18,2) null, 7 gender char(3) not null default(1), 8 birthday datetime null, 9 createtime datetime not null default(getdate()) 10 ) 11 12 insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com','cctext','123456',9000999999999.99,'1','1987-01-01',getdate()); 13 insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com1','cctext2','123456',9000999999999.99,'1','1987-01-01',getdate()); 14 insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com11','cctext22','123456',9000999999999.99,'1','1987-01-01',getdate()); 15 insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com111','cctext22','123456',9000999999999.99,'1','1987-01-01',getdate());
- bcp 方式：
要是用bcp操作的话，我们需要在cmd中进行执行，太繁琐，所以我们采用xp_cmdshell方式来执行bcp命令操作。
在使用xp_cmdshell需要开启sqlserver show_advanced options 配置信息：
1 -- 开启批量导入功能 xp_cmdshell 2 -- find 'show advanced options' config option from sys.configurations 3 select * from sys.configurations where name='show advanced options'; 4 go 5 exec sp_configure 'show_advanced options',1; 6 reconfigure 7 go 8 exec sp_configure 'xp_cmdshell',1; 9 reconfigure 10 go 11 select * from sys.configurations where name='show advanced options';
执行上边命令返回结果信息：
接下来，我们使用xp_cmdshell来执行bcp导出、导入数据操作：
1 use test_bulkinsert; 2 -- 导入 dbo.Member中记录到文件 d:/member.txt 中 3 exec master..xp_cmdshell 'BCP test_bulkinsert.dbo.Member out d:/member.txt -c -S. etwork -Usa -Pnew.1234' 4 5 --begin transaction x1 6 truncate table dbo.Member; 7 select * from dbo.Member; 8 exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member in d:/member.txt -c -S. etwork -Usa -Pnew.1234' 9 select * from dbo.Member; 10 --rollback transaction x1;
执行后返回结果信息：
查看文件member.txt信息：
1 yy3b2007com cctext 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.443
2 yy3b2007com1 cctext2 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447
3 yy3b2007com11 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447
4 yy3b2007com111 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447
备注：列之间使用' '间隔，换行符为‘ ’
上边的例子，导出导入的过程包含有id自增列，那么怎么实现导入过程中不包含自增列，让数据库内部自己维护呢？
我们需要使用formatfile---sqlserver格式化文件。具体怎么生成可以参考：https://msdn.microsoft.com/zh-cn/library/ms191516.aspx
格式化文件格式包含两种：一种是非xml格式，一种是xml格式。
A、导入member.txt数据源，并编辑。
执行命令：
use test_bulkinsert; -- 导入 dbo.Member中记录到文件 d:/member.txt 中 exec master..xp_cmdshell 'BCP test_bulkinsert.dbo.Member out d:/member.txt -c -T -S. etwork -Usa -Pnew.1234'
生成文件member.txt.
1 yy3b2007com cctext 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 2 yy3b2007com1 cctext2 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 3 yy3b2007com11 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 4 yy3b2007com111 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180
编辑为：
yy3b2007com cctext 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 yy3b2007com1 cctext2 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 yy3b2007com11 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180 yy3b2007com111 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 01:39:58.180
B、生成非xml格式化文件：
执行命令：
-- 生成文件member-f-c-x.xml文件，编辑为member-f-c-x-without-id.Xml exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member format nul -S. etwork -Usa -Pnew.1234 -c -f d:/member-f-c.Xml -T'
修改非xml格式化文件member-f-c.Xml
原始文件信息为：
10.0 8 1 SQLCHAR 0 21 " " 1 id "" 2 SQLCHAR 0 128 " " 2 name Chinese_PRC_CI_AS 3 SQLCHAR 0 128 " " 3 nickname Chinese_PRC_CI_AS 4 SQLCHAR 0 128 " " 4 pwd Chinese_PRC_CI_AS 5 SQLCHAR 0 41 " " 5 moneyicon "" 6 SQLCHAR 0 3 " " 6 gender Chinese_PRC_CI_AS 7 SQLCHAR 0 24 " " 7 birthday "" 8 SQLCHAR 0 24 " " 8 createtime ""
修改为：
10.0 7 1 SQLCHAR 0 128 " " 2 name Chinese_PRC_CI_AS 2 SQLCHAR 0 128 " " 3 nickname Chinese_PRC_CI_AS 3 SQLCHAR 0 128 " " 4 pwd Chinese_PRC_CI_AS 4 SQLCHAR 0 41 " " 5 moneyicon "" 5 SQLCHAR 0 3 " " 6 gender Chinese_PRC_CI_AS 6 SQLCHAR 0 24 " " 7 birthday "" 7 SQLCHAR 0 24 " " 8 createtime ""
C、执行导入保留标识值：
1 truncate table dbo.Member; 2 select * from dbo.Member; 3 exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member in d:/member.txt -f d:/member-f-c.xml -E -T -S. etwork -Usa -Pnew.1234' 4 select * from dbo.Member;
- Bulk Insert From .. With(...) 方式：
BULK INSERT微软官网上看到是SQLSERVER2008才开启的功能，但是仔细查看发现SQLSERVER2005也支持该操作。
DSDN语法参考英文地址：https://msdn.microsoft.com/en-us/library/ms188365.aspx
DSDN语法参考中文地址：https://msdn.microsoft.com/zh-cn/library/ms188365.aspx
A、生成xml格式化文件：
exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member format nul -S. etwork -Usa -Pnew.1234 -c -t -x -f d:/myTestFormatFiles.Xml -T'
myTestFormatFiles.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="21"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="41"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/> <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="id" xsi:type="SQLBIGINT"/> <COLUMN SOURCE="2" NAME="name" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="nickname" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="4" NAME="pwd" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="5" NAME="moneyicon" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/> <COLUMN SOURCE="6" NAME="gender" xsi:type="SQLCHAR"/> <COLUMN SOURCE="7" NAME="birthday" xsi:type="SQLDATETIME"/> <COLUMN SOURCE="8" NAME="createtime" xsi:type="SQLDATETIME"/> </ROW> </BCPFORMAT>
修改，并把修改后的信息保存在myTestFormatFiles_without_id.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="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="41"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="name" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="2" NAME="nickname" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="pwd" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="4" NAME="moneyicon" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/> <COLUMN SOURCE="5" NAME="gender" xsi:type="SQLCHAR"/> <COLUMN SOURCE="6" NAME="birthday" xsi:type="SQLDATETIME"/> <COLUMN SOURCE="7" NAME="createtime" xsi:type="SQLDATETIME"/> </ROW> </BCPFORMAT>
修改member.txt，并把修改后的信息保存在member_without_id.txt
yy3b2007com cctext 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.443 yy3b2007com1 cctext2 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447 yy3b2007com11 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447 yy3b2007com111 cctext22 123456 9000999999999.99 1 1987-01-01 00:00:00.000 2016-07-27 00:41:48.447
B、执行插入命令：
use test_bulkinsert truncate table dbo.Member; bulk insert dbo.Member from 'd://member_without_id.txt' with( formatfile='d://myTestFormatFiles_without_id.Xml' ) select * from dbo.Member;
- Insert Into ... (field1name,field2name...) select field1name,field2name... from openrowset(bulk 'xxx',formatfile='xxx') 方式：
可以使用Bulk Insert方式格式化文件，和数据文件来测试：
use test_bulkinsert truncate table dbo.Member; insert into dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime) select * from openrowset(bulk 'd://member_without_id.txt',formatfile='d://myTestFormatFiles_without_id.Xml') as t select * from dbo.Member;
以上就是SQLServer三种方式实现大容量导入数据时保留标识值的完整示例。
- 完整代码测试：
USE [test_bulkinsert] GO /****** Object: Table [dbo].[Member] Script Date: 07/27/2016 02:07:26 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Member__gender__0EA330E9]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Member] DROP CONSTRAINT [DF__Member__gender__0EA330E9] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Member__createti__0F975522]') AND type = 'D') BEGIN ALTER TABLE [dbo].[Member] DROP CONSTRAINT [DF__Member__createti__0F975522] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Member]') AND type in (N'U')) DROP TABLE [dbo].[Member] GO /****** Object: Table [dbo].[Member] Script Date: 07/27/2016 02:07:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Member]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Member]( [id] [bigint] IDENTITY(1,1) NOT NULL, [name] [nvarchar](64) NOT NULL, [nickname] [nvarchar](64) NULL, [pwd] [nvarchar](64) NOT NULL, [moneyicon] [decimal](18, 2) NULL, [gender] [char](3) NOT NULL DEFAULT ((1)), [birthday] [datetime] NULL, [createtime] [datetime] NOT NULL DEFAULT (getdate()), PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com','cctext','123456',9000999999999.99,'1','1987-01-01',getdate()); insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com1','cctext2','123456',9000999999999.99,'1','1987-01-01',getdate()); insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com11','cctext22','123456',9000999999999.99,'1','1987-01-01',getdate()); insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com111','cctext22','123456',9000999999999.99,'1','1987-01-01',getdate()); -- 开启批量导入功能 xp_cmdshell -- find 'show advanced options' config option from sys.configurations select * from sys.configurations where name='show advanced options'; go exec sp_configure 'show_advanced options',1; reconfigure go exec sp_configure 'xp_cmdshell',1; reconfigure go select * from sys.configurations where name='show advanced options'; use test_bulkinsert; -- 导入 dbo.Member中记录到文件 d:/member.txt 中 exec master..xp_cmdshell 'BCP test_bulkinsert.dbo.Member out d:/member.txt -c -T -S. etwork -Usa -Pnew.1234' -- 生成文件member-f-c-x.xml文件，编辑为member-f-c-x-without-id.Xml exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member format nul -S. etwork -Usa -Pnew.1234 -c -f d:/member-f-c.Xml -T' --begin transaction x1 truncate table dbo.Member; select * from dbo.Member; exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member in d:/member.txt -f d:/member-f-c.xml -E -T -S. etwork -Usa -Pnew.1234' select * from dbo.Member; --rollback transaction x1; exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member format nul -S. etwork -Usa -Pnew.1234 -c -t -x -f d:/myTestFormatFiles.Xml -T' use test_bulkinsert truncate table dbo.Member; bulk insert dbo.Member from 'd://member_without_id.txt' with( formatfile='d://myTestFormatFiles_without_id.Xml' ) select * from dbo.Member; use test_bulkinsert truncate table dbo.Member; insert into dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime) select * from openrowset(bulk 'd://member_without_id.txt',formatfile='d://myTestFormatFiles_without_id.Xml') as t select * from dbo.Member;
- 具体参考资料：
MSDN BULK INSERT (Transact-SQL)：https://msdn.microsoft.com/zh-cn/zh-ch/library/ms188365.aspx
MSDN 大容量导入数据时保留标识值 (SQL Server):https://msdn.microsoft.com/zh-cn/library/ms186335.aspx
MSDN 创建格式化文件 (SQL Server):https://msdn.microsoft.com/zh-cn/library/ms191516.aspx
MSDN 使用格式化文件大容量导入数据 (SQL Server):https://msdn.microsoft.com/zh-cn/library/ms178129.aspx