SQLSERVER:大容量导入数据时保留标识值 (SQL Server)

从MSDN上看到实现大容量导入数据时保留标识值得方法包含三种：

MSDN链接地址为：https://msdn.microsoft.com/zh-cn/library/ms178129.aspx

感觉MSDN上给的列子都没有数据，有些demo不直接，所以这里我要写例子来实现这三种方式。

  1. bcp
  2. Bulk Insert From .. With(...)
  3. 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.\network -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.\network -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

备注：列之间使用'\t'间隔，换行符为‘\r\n’

上边的例子，导出导入的过程包含有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.\network -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.\network -Usa -Pnew.1234 -c -f d:/member-f-c.Xml -T'

 

修改非xml格式化文件member-f-c.Xml

原始文件信息为：

10.0
8
1       SQLCHAR             0       21      "\t"      1     id                       ""
2       SQLCHAR             0       128     "\t"      2     name                     Chinese_PRC_CI_AS
3       SQLCHAR             0       128     "\t"      3     nickname                 Chinese_PRC_CI_AS
4       SQLCHAR             0       128     "\t"      4     pwd                      Chinese_PRC_CI_AS
5       SQLCHAR             0       41      "\t"      5     moneyicon                ""
6       SQLCHAR             0       3       "\t"      6     gender                   Chinese_PRC_CI_AS
7       SQLCHAR             0       24      "\t"      7     birthday                 ""
8       SQLCHAR             0       24      "\r\n"    8     createtime               ""

修改为：

10.0
7
1       SQLCHAR             0       128     "\t"     2     name                     Chinese_PRC_CI_AS
2       SQLCHAR             0       128     "\t"     3     nickname                 Chinese_PRC_CI_AS
3       SQLCHAR             0       128     "\t"     4     pwd                      Chinese_PRC_CI_AS
4       SQLCHAR             0       41      "\t"     5     moneyicon                ""
5       SQLCHAR             0       3       "\t"     6     gender                   Chinese_PRC_CI_AS
6       SQLCHAR             0       24      "\t"     7     birthday                 ""
7       SQLCHAR             0       24      "\r\n"   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.\network -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.\network -Usa -Pnew.1234 -c -t\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="\t" MAX_LENGTH="21"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="24"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" 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="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="128" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="24"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\r\n" 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.\network -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.\network -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.\network -Usa -Pnew.1234'
select * from dbo.Member;
--rollback transaction x1;







exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member format nul -S.\network -Usa -Pnew.1234 -c -t\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

posted @ 2016-07-27 02:02  cctext  阅读(1213)  评论(0)  编辑  收藏  举报