从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')
下边我们就三种方式展开测试:
整理数据源:
create table dbo.Member(
id bigint identity(1,1) primary key 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())
) insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com','cctext','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com1','cctext2','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com11','cctext22','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com111','cctext22','',9000999999999.99,'','1987-01-01',getdate());
- bcp 方式:
要是用bcp操作的话,我们需要在cmd中进行执行,太繁琐,所以我们采用xp_cmdshell方式来执行bcp命令操作。
在使用xp_cmdshell需要开启sqlserver show_advanced options 配置信息:
-- 开启批量导入功能 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';
执行上边命令返回结果信息:
接下来,我们使用xp_cmdshell来执行bcp导出、导入数据操作:
use test_bulkinsert;
-- 导入 dbo.Member中记录到文件 d:/member.txt 中
exec master..xp_cmdshell 'BCP test_bulkinsert.dbo.Member out d:/member.txt -c -S.\network -Usa -Pnew.1234' --begin transaction x1
truncate table dbo.Member;
select * from dbo.Member;
exec master..xp_cmdshell 'bcp test_bulkinsert.dbo.Member in d:/member.txt -c -S.\network -Usa -Pnew.1234'
select * from dbo.Member;
--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、执行导入保留标识值:
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;
- 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','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com1','cctext2','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com11','cctext22','',9000999999999.99,'','1987-01-01',getdate());
insert dbo.Member(name,nickname,pwd,moneyicon,gender,birthday,createtime)values('yy3b2007com111','cctext22','',9000999999999.99,'','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