SQL Server 批量生成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

 

但是如果我们需要导出类似这些表有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

 

下面是生成的脚本：

--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

--下面的省略掉了.