首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

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

2012-05-01 
【BCP过滤导出数据表】请帮忙解答,谢谢!大概意思是这样的:数据库里有有A,B,C...表,现在需要导出里面的B表应

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


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


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

SQL code
EXEC sp_configure 'show advanced options', 1GO-- 重新配置RECONFIGUREGO-- 启用xp_cmdshellEXEC sp_configure 'xp_cmdshell', 1GO--重新配置RECONFIGUREGOcreate proc sp_test @path varchar(255),@svrname varchar(255),@username varchar(255),@password varchar(255),@indbname varchar(255)asselect * from sysdatabases where dbid > 6declare @dbname varchar(255)declare @tablename varchar(255)declare @cmdline varchar(255)declare @creatpath varchar(255)select @creatpath = 'md ' + @pathexec master..xp_cmdshell @creatpathexec ('declare all_cursor cursorfor select name from sysdatabases where dbid > 6 and upper(name) like ''%'+@indbname+'%''')open all_cursorfetch all_cursor into @dbnamewhile @@fetch_status = 0begin       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 @dbnameendclose all_cursordeallocate all_cursorGO


[解决办法]
SQL code
EXEC sp_configure 'show advanced options', 1GO-- 重新配置RECONFIGUREGO-- 启用xp_cmdshellEXEC sp_configure 'xp_cmdshell', 1GO--重新配置RECONFIGUREGOcreate proc sp_test @path varchar(255),@svrname varchar(255),@username varchar(255),@password varchar(255),@indbname varchar(255)asselect * from sysdatabases where dbid > 6declare @dbname varchar(255)declare @tablename varchar(255)declare @cmdline varchar(255)declare @creatpath varchar(255)select @creatpath = 'md ' + @pathexec master..xp_cmdshell @creatpathexec ('declare all_cursor cursorfor select name from sysdatabases where dbid > 6 and upper(name) like ''%'+@indbname+'%''')open all_cursorfetch all_cursor into @dbnamewhile @@fetch_status = 0begin       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 @dbnameendclose all_cursordeallocate all_cursorGO 

热点排行