【BCP过滤导出数据表】请帮忙解答，多谢-MSSQL教程-爱易网页

【BCP过滤导出数据表】请帮忙解答，多谢

日期：2014-05-18　浏览次数：20470 次

【BCP过滤导出数据表】请帮忙解答，谢谢！
大概意思是这样的：数据库里有有A，B，C...表，现在需要导出里面的B表应该怎么写呢？

大概思路是：给个列表，然后按照列表来导出，但是小弟笨拙，不知道如何入手。所以来这里请教大家，寻求帮助。

下面的可以把数据库里面的表全部导出，起不到过滤的作用：如何在这里面加过滤条件呢？

SQL code



EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO
create proc sp_test @path varchar(255),@svrname varchar(255),@username varchar(255),@password varchar(255),@indbname varchar(255)
as
select * from sysdatabases where dbid > 6

declare @dbname varchar(255)
declare @tablename varchar(255)
declare @cmdline varchar(255)

declare @creatpath varchar(255)
select @creatpath = 'md ' + @path
exec master..xp_cmdshell @creatpath

exec ('declare all_cursor cursor
for select name from sysdatabases where dbid > 6 and upper(name) like ''%'+@indbname+'%''')


open all_cursor
fetch all_cursor into @dbname

while @@fetch_status = 0
begin
    
   print @dbname
   select @cmdline = 'md ' + @path + '\'+ @dbname 
   exec master..xp_cmdshell @cmdline
   exec ('declare tbl_cursor cursor for
   select name from ' + @dbname +'.dbo.sysobjects where xtype = ''U''')
   
   open tbl_cursor
   fetch tbl_cursor into @tablename
   while @@fetch_status = 0
   begin
    print @tablename
    select @cmdline = 'bcp "'+ @dbname + '..' + @tablename + '" out "' + @path + '\' + @dbname + '\' +@tablename + '.txt" -c -U' + @username +' -P'+@password  +' -S ' + @svrname
        print @cmdline
        exec master..xp_cmdshell @cmdline
        fetch tbl_cursor into @tablename
   end
   close tbl_cursor
   deallocate tbl_cursor
   fetch all_cursor into @dbname
end
close all_cursor
deallocate all_cursor

GO

------解决方案--------------------

SQL code

EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO
create proc sp_test @path varchar(255),@svrname varchar(255),@username varchar(255),@password varchar(255),@indbname varchar(255)
as
select * from sysdatabases where dbid > 6

declare @dbname varchar(255)
declare @tablename varchar(255)
declare @cmdline varchar(255)

declare @creatpath varchar(255)
select @creatpath = 'md ' + @path
exec master..xp_cmdshell @creatpath

exec ('declare all_cursor cursor
for select name from sysdatabases where dbid > 6 
and upper(name) like ''%'+@indbname+'%''')


open all_cursor
fetch all_cursor into @dbname

while @@fetch_status = 0
begin
    
   print @dbname
   select @cmdline = 'md ' + @path + '\'+ @dbname 
   exec master..xp_cmdshell @cmdline
   exec ('declare tbl_cursor cursor for
   select name from ' + @dbname +'.dbo.sysobjects where xtype = ''U'' 
--and name in(''你想要导出表的名称'')')
   
   open tbl_cursor
   fetch tbl_cursor into @tablename
   while @@fetch_status = 0
   begin
    print @tablename
    select @cmdline = 'bcp "'+ @dbname + '..' + @tablename + '" out "' + @path + '\' + @dbname + '\' +@tablename + '.txt" -c -U' + @username +' -P'+@password  +' -S ' + @svrname
        print @cmdline
        exec master..xp_cmdshell @cmdline
        fetch tbl_cursor into @tablename
   end
   close tbl_cursor
   deallocate tbl_cursor
   fetch all_cursor into @dbname
end
close all_cursor
deallocate all_cursor

GO

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

【BCP过滤导出数据表】请帮忙解答，多谢

相关资料更多>

推荐阅读更多>