批量生成bcp命令
特别说明一下,这个批量生成bcp命令是有一些条件限制的:需要导出数据的表的名称是要有规律的,表的名称是需要连续、不间断的,表名最好是以数字来递增的,不过也可以是字母的。
我之所以会写这个批量生成SQL是因为我数据库中表名类似于:RawActivatePDPContextReq_0401,RawActivatePDPContextReq_0402,RawActivatePDPContextReq_0403等等,所以下面的SQL对大部分人来说没有什么意义,但我想表达的是:我们可以SQL来解决一些问题。
图1是一个适用使用本文这个脚本的数据库表列表,我们的需求是为gbRawActivate2ndPDPContextAcpt_0501这几个类似的表生成bcp导出表数据到csv的SQL命令。如果你还不了解什么是bcp命令,可以参考:使用bcp进行大数据量导出导入
(图1:适用环境)
写一个表的bcp命令是很简单的(如下面的脚本)
--
这是一个模板
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0401
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0401" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0401.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0401
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0401" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0401.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
但是如果我们需要导出类似这些表有20个、50个呢?我们就需要去拷贝下面的SQL,再一次次去修改表名、导出的路径等,而且这样手动修改的方式是很容易出错的,说不定你只修改了表名,没有修改到路径呢,权衡种种,我们需要一些智能一点的东西来确保我们SQL的准确性。其实这个是可以通过:使用Excel批量生成SQL脚本(小技巧)来解决这个,但是这个使用起来不方便,而且出错的几率也是很多的,所以就使用了动态的拼凑出脚本的SQL:
--
=============================================
-- Author: <Viajar>
-- Create date: <2010/06/01>
-- Description: <生成bcp导出的字符串>
-- Remark:
-- 适用于:连续、有规律表名的bcp命令
-- 表名 = 表名前缀 + 起始表后缀 进行分离
-- 路径 = 路径前缀 + 起始表后缀 进行分离
-- =============================================
DECLARE @tableNamePrefix VARCHAR ( 50 ); -- 表名前缀:temp_gb2.dbo.gbRawActivatePDPContextReq_04
DECLARE @savePathPrefix VARCHAR ( 50 ); -- 路径前缀:I:\gbForKevin\gbRawActivatePDPContextReq_04
DECLARE @start int ; -- 起始表后缀:01
DECLARE @end int ; -- 截至表后缀:20
DECLARE @dataBase VARCHAR ( 50 ); -- 数据库实例:BW-DATA\BOTWAVE
DECLARE @user VARCHAR ( 50 ); -- 数据库用户登陆名:sa
DECLARE @password VARCHAR ( 50 ); -- 密码:password
DECLARE @splitString VARCHAR ( 50 ); -- csv文件的分隔符:|
-- 设置变量
SET @tableNamePrefix = ' temp_gb2.dbo.gbRawActivatePDPContextReq_04 '
SET @savePathPrefix = ' I:\gbForKevin\gbRawActivatePDPContextReq_04 '
SET @start = 01
SET @end = 20
SET @dataBase = ' BW-DATA\BOTWAVE '
SET @user = ' sa '
SET @password = ' password '
SET @splitString = ' | '
BEGIN
DECLARE @i INT ; -- 用于循环的计数器.
DECLARE @iString VARCHAR ( 5 ); -- @i的字符串变量.
DECLARE @sql VARCHAR ( max ); -- 保存生成的SQL.
SET @i = @start
WHILE ( @i <= @end )
BEGIN
SET @iString = CONVERT ( varchar ( 5 ), @i )
IF ( len ( @iString ) = 1 )
BEGIN
SET @iString = ' 0 ' + @iString
END
SET @sql = ' EXEC master..xp_cmdshell '' bcp "SELECT * FROM ' ;
SET @sql = @sql + @tableNamePrefix + @iString ; -- @tableNamep
SET @sql = @sql + ' " queryout ' + @savePathPrefix + @iString + ' .csv ' ; -- @savePath
SET @sql = @sql + ' -S ' + @dataBase + ' -U" ' + @user + ' " -P" ' + @password + ' " ' ; -- @dataBase
SET @sql = @sql + ' -c -t" ' + @splitString + ' " -r"\n" -k -b10000 -a65535 ''' ; -- other parameter
PRINT ' -- ' + @tableNamePrefix + @iString
PRINT @sql
PRINT ' GO '
PRINT char ( 13 ) -- 换行符.
SET @i = @i + 1
END
END
-- Author: <Viajar>
-- Create date: <2010/06/01>
-- Description: <生成bcp导出的字符串>
-- Remark:
-- 适用于:连续、有规律表名的bcp命令
-- 表名 = 表名前缀 + 起始表后缀 进行分离
-- 路径 = 路径前缀 + 起始表后缀 进行分离
-- =============================================
DECLARE @tableNamePrefix VARCHAR ( 50 ); -- 表名前缀:temp_gb2.dbo.gbRawActivatePDPContextReq_04
DECLARE @savePathPrefix VARCHAR ( 50 ); -- 路径前缀:I:\gbForKevin\gbRawActivatePDPContextReq_04
DECLARE @start int ; -- 起始表后缀:01
DECLARE @end int ; -- 截至表后缀:20
DECLARE @dataBase VARCHAR ( 50 ); -- 数据库实例:BW-DATA\BOTWAVE
DECLARE @user VARCHAR ( 50 ); -- 数据库用户登陆名:sa
DECLARE @password VARCHAR ( 50 ); -- 密码:password
DECLARE @splitString VARCHAR ( 50 ); -- csv文件的分隔符:|
-- 设置变量
SET @tableNamePrefix = ' temp_gb2.dbo.gbRawActivatePDPContextReq_04 '
SET @savePathPrefix = ' I:\gbForKevin\gbRawActivatePDPContextReq_04 '
SET @start = 01
SET @end = 20
SET @dataBase = ' BW-DATA\BOTWAVE '
SET @user = ' sa '
SET @password = ' password '
SET @splitString = ' | '
BEGIN
DECLARE @i INT ; -- 用于循环的计数器.
DECLARE @iString VARCHAR ( 5 ); -- @i的字符串变量.
DECLARE @sql VARCHAR ( max ); -- 保存生成的SQL.
SET @i = @start
WHILE ( @i <= @end )
BEGIN
SET @iString = CONVERT ( varchar ( 5 ), @i )
IF ( len ( @iString ) = 1 )
BEGIN
SET @iString = ' 0 ' + @iString
END
SET @sql = ' EXEC master..xp_cmdshell '' bcp "SELECT * FROM ' ;
SET @sql = @sql + @tableNamePrefix + @iString ; -- @tableNamep
SET @sql = @sql + ' " queryout ' + @savePathPrefix + @iString + ' .csv ' ; -- @savePath
SET @sql = @sql + ' -S ' + @dataBase + ' -U" ' + @user + ' " -P" ' + @password + ' " ' ; -- @dataBase
SET @sql = @sql + ' -c -t" ' + @splitString + ' " -r"\n" -k -b10000 -a65535 ''' ; -- other parameter
PRINT ' -- ' + @tableNamePrefix + @iString
PRINT @sql
PRINT ' GO '
PRINT char ( 13 ) -- 换行符.
SET @i = @i + 1
END
END
下面是生成的脚本:
--
temp_gb2.dbo.gbRawActivatePDPContextReq_0401
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0401" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0401.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0402
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0402" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0402.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0403
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0403" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0403.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0404
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0404" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0404.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- 下面的省略掉了.
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0401" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0401.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0402
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0402" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0402.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0403
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0403" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0403.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- temp_gb2.dbo.gbRawActivatePDPContextReq_0404
EXEC master..xp_cmdshell ' bcp "SELECT * FROM temp_gb2.dbo.gbRawActivatePDPContextReq_0404" queryout I:\gbForKevin\gbRawActivatePDPContextReq_0404.csv -S BW-DATA\BOTWAVE -U"sa" -P"password" -c -t"|" -r"\n" -k -b10000 -a65535 '
GO
-- 下面的省略掉了.