if exists(select 1 from sysobjects where name='file2table' and objectproperty(id,'isprocedure')=1) drop procedure file2table go create procedure file2table @servername varchar(200) --服务器名称 ,@username varchar(200) --用户名,如果用nt验证方式,则为空'' ,@password varchar(200) --密码 ,@tbname varchar(500) --数据库 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt ,@isout bit --1为导出,0为导入 as declare @sql varchar(8000)------如果@tbname参数为ksoa类型,则默认导出整个数据库中的数据,如果@tbname参数为ksoa..spkfk则默认导出某个表的数据 begin --导出整个数据库,定义游标,取出所有的用户表 declare @m_tbname varchar(250) if right(@filename,1)<>'\' set @filename=@filename+'\'--如果不是文件夹根目录,设置为根目录 set @m_tbname='declare #tb cursor for select name from '+@tbname+'.dbo.sysobjects where xtype=''u'' and (name not like ''wms_%'' and name not like ''HR_%'' and name not like ''tmp_%'')' exec(@m_tbname) open #tb fetch next from #tb into @m_tbname while @@fetch_status=0 begin set @sql='bcp '+@tbname+'.'+'dbo.'+@m_tbname+case when @isout=1 then ' out ' else ' in ' end + @filename+@m_tbname+'.txt -w' +' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end +' /P '+isnull(@password,'') exec master.dbo.xp_cmdshell @sql fetch next from #tb into @m_tbname end close #tb deallocate #tb end go ----导入整个数据库 exec file2table 'yangxing','sa','','KSOA','E:\Music',1 ----导入 exec file2table '127.0.0.1','sa','','ksoa','E:\admin\',1这样就可以全部导出了
drop procedure file2table
go
create procedure file2table
@servername varchar(200) --服务器名称
,@username varchar(200) --用户名,如果用nt验证方式,则为空''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)------如果@tbname参数为ksoa类型,则默认导出整个数据库中的数据,如果@tbname参数为ksoa..spkfk则默认导出某个表的数据
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'\' set @filename=@filename+'\'--如果不是文件夹根目录,设置为根目录
set @m_tbname='declare #tb cursor for select name from '+@tbname+'.dbo.sysobjects where xtype=''u''
and (name not like ''wms_%'' and name not like ''HR_%'' and name not like ''tmp_%'')'
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'.'+'dbo.'+@m_tbname+case when @isout=1 then ' out ' else ' in ' end
+ @filename+@m_tbname+'.txt -w'
+' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master.dbo.xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
----导入整个数据库
exec file2table 'yangxing','sa','','KSOA','E:\Music',1
----导入
exec file2table '127.0.0.1','sa','','ksoa','E:\admin\',1这样就可以全部导出了
结构的话,脚本,
数据的话,有DTS