很多时候，需要批量的导数据，可能大家想到的第一反应就是右键数据库->任务->导入导出数据。但是其实微软自身提供的大容量导入导出工具,有bcp, bulkinsert 之类的也是很好用。今天整理一下bcp 的常规用法
首先bcp 的介绍页，请：https://msdn.microsoft.com/zh-cn/library/aa337544(v=sql.120).aspx
首先我们先搞个测试表来做测试
CREATE TABLE [dbo].[t3]( [ID] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NULL, [Chinese] [int] NULL, [Math] [int] NULL ) ON [PRIMARY] go CREATE TABLE [dbo].[t4]( [ID] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NULL, [Chinese] [int] NULL, [Math] [int] NULL ) ON [PRIMARY] go USE [Test] GO INSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'张三', 90, 80) GO INSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'李四', 75, 90) GO INSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'王五', 68, 100) GO INSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES (N'赵六', 100, NULL) GO INSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'うずまき ナルト ', 59, 80) GO
1 、首先介绍最简单直接导出文件的方法，直接使用导出字符的方式进行导出导入
--字符串导出 exec sys.xp_cmdshell 'bcp test.dbo.t3 out D: 3.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123 -c' --采用刚刚导出的文件进行导入 exec sys.xp_cmdshell 'bcp test.dbo.t4 in D: 3.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123 -c'
这个是最基础的写法。如果要在cmd黑屏里面，就执行红色部分即可。
执行完之后，可以在t4里面查到跟t3 一模一样的数据了。
这里有几个地方需要说明一下。 -U user -P pwd 这里是输入用户名和密码，假如在本机上面操作，可以使用-T 来代替。
默认情况下，bcp 采用的是 tab 作为列分割， 回车换行作为行分割，可能有些表里面存放的数据本身带有 tab 或者 回车的，这样就会影响到数据的导入(导出是肯定没有问题的)，
这个时候就需要使用 -t 和 -r 的选项了， -t @@#@ 表示使用@@#@ 作为列分割。-r $$% 表示使用 $$%作为行分割。看起来这些符号有点奇葩，但是一般来说，还是建议用不常用的符号作为分割符。避免和平常的数据冲突。
还有一点，对于空值 null ，导出数据会转换成空字符，导入的话空字符会转换成null
2、使用 queryout 方式导出文本数据
exec sys.xp_cmdshell 'bcp "select ID,Name,Math from test.dbo.t3" queryout D: 3.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123 -c'
这样的写法，只导出来三列，其中Chinese列是没有导出到这个文本的。
PS: queryout 并没有对应的queryin ，所以如果是后续需要重新导入，慎用这种写法
3、使用格式化的文件来导出/导入 数据
除了使用直接导出，还可以使用格式化文件进行导出导入。分成3步处理
--格式化文件生成,导出/导入，非xml格式 exec sys.xp_cmdshell 'bcp test.dbo.t3 format nul -f D:format.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123 -c' --导出文件 exec sys.xp_cmdshell 'bcp test.dbo.t3 out D: 3.txt -f D:format.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123' --导入文件 exec sys.xp_cmdshell 'bcp test.dbo.t4 in D: 3.txt -f D:format.txt -S GINLATOPMSSQLSERVER2016 -U sa -P 123'
首先第一个语句生成了一个t3的格式化文件，生成的内容是这样纸的
12.0 4 1 SQLCHAR 0 12 " " 1 ID "" 2 SQLCHAR 0 100 " " 2 name Chinese_PRC_BIN2 3 SQLCHAR 0 12 " " 3 Chinese "" 4 SQLCHAR 0 12 " " 4 Math ""
关于这块的解释，可以戳这里：https://msdn.microsoft.com/zh-cn/library/ms191479(v=sql.120).aspx
这样的好处是有个格式化文件。看起来比较清晰。
4、使用格式化文件来导出导入数据，步骤差不多。差别就是格式化文件使用的是xml 。具体实践如下
--格式化文件生成,xml格式 exec sys.xp_cmdshell 'bcp test.dbo.t3 format nul -f D:format.xml -x -S GINLATOPMSSQLSERVER2016 -U sa -P 123 -c' --格式化文件导出 exec sys.xp_cmdshell 'bcp test.dbo.t3 out D: 3.txt -f D:format.xml -S GINLATOPMSSQLSERVER2016 -U sa -P 123 ' --格式化文件导入 exec sys.xp_cmdshell 'bcp test.dbo.t4 in D: 3.txt -f D:format.xml -S GINLATOPMSSQLSERVER2016 -U sa -P 123 '
生成的记录就是这个样纸，解释的部分可以戳会之前的那个链接
<?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="12"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="100" COLLATION="Chinese_PRC_BIN2"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="12"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="12"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/> <COLUMN SOURCE="2" NAME="name" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="Chinese" xsi:type="SQLINT"/> <COLUMN SOURCE="4" NAME="Math" xsi:type="SQLINT"/> </ROW> </BCPFORMAT>
本次分享到这里