笔记整理之BCP

很多时候，需要批量的导数据，可能大家想到的第一反应就是右键数据库->任务->导入导出数据。但是其实微软自身提供的大容量导入导出工具,有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]goCREATE TABLE [dbo].[t4](  [ID] [int] IDENTITY(1,1) NOT NULL,  [name] [nvarchar](50) NULL,  [Chinese] [int] NULL,  [Math] [int] NULL) ON [PRIMARY]goUSE [Test]GOINSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'张三', 90, 80)GOINSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'李四', 75, 90)GOINSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'王五', 68, 100)GOINSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES (N'赵六', 100, NULL)GOINSERT [dbo].[t3] ( [name], [Chinese], [Math]) VALUES ( N'うずまき ナルト ', 59, 80)GO

1 、首先介绍最简单直接导出文件的方法，直接使用导出字符的方式进行导出导入

--字符串导出exec sys.xp_cmdshell 'bcp test.dbo.t3 out D:\t3.txt -S GINLATOP\MSSQLSERVER2016 -U sa -P 123 -c' --采用刚刚导出的文件进行导入exec sys.xp_cmdshell 'bcp test.dbo.t4 in D:\t3.txt -S GINLATOP\MSSQLSERVER2016 -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:\t3.txt -S GINLATOP\MSSQLSERVER2016 -U sa -P 123 -c' 

这样的写法，只导出来三列，其中Chinese列是没有导出到这个文本的。

PS: queryout 并没有对应的queryin ，所以如果是后续需要重新导入，慎用这种写法

3、使用格式化的文件来导出/导入 数据

除了使用直接导出，还可以使用格式化文件进行导出导入。分成3步处理

--格式化文件生成,导出/导入，非exec sys.xp_cmdshell 'bcp test.dbo.t3 format nul -f D:\format.txt -S GINLATOP\MSSQLSERVER2016 -U sa -P 123 -c' --导出文件exec sys.xp_cmdshell 'bcp test.dbo.t3 out D:\t3.txt -f D:\format.txt -S GINLATOP\MSSQLSERVER2016 -U sa -P 123' --导入文件exec sys.xp_cmdshell 'bcp test.dbo.t4 in D:\t3.txt -f D:\format.txt -S GINLATOP\MSSQLSERVER2016 -U sa -P 123' 

首先第一个语句生成了一个t3的格式化文件，生成的内容是这样纸的

12.041    SQLCHAR       0    12   "\t"   1   ID         ""2    SQLCHAR       0    100   "\t"   2   name        Chinese_PRC_BIN23    SQLCHAR       0    12   "\t"   3   Chinese      ""4    SQLCHAR       0    12   "\r\n"  4   Math        ""

关于这块的解释，可以戳这里：https://msdn.microsoft.com/zh-cn/library/ms191479(v=sql.120).aspx

这样的好处是有个格式化文件。看起来比较清晰。

4、使用格式化文件来导出\导入数据，步骤差不多。差别就是格式化文件使用的是

--格式化文件生成,exec sys.xp_cmdshell 'bcp test.dbo.t3 format nul -f D:\format.' --格式化文件导出exec sys.xp_cmdshell 'bcp test.dbo.t3 out D:\t3.txt -f D:\format.' --格式化文件导入exec sys.xp_cmdshell 'bcp test.dbo.t4 in D:\t3.txt -f D:\format.' 

生成的记录就是这个样纸，解释的部分可以戳会之前的那个链接

<??><BCPFORMAT ="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" ="http://www.w3.org/2001/> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="Chinese_PRC_BIN2"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" 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>

本次分享到这里

原标题：笔记整理之BCP

关键词：