1 if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
2 drop procedure File2Table
3 go
4 create procedure File2Table
5 @servername varchar(200) --服务器名
6 ,@username varchar(200) --用户名,如果用NT验证方式,则为空''
7 ,@password varchar(200) --密码
8 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
9 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
10 ,@isout bit --1为导出,0为导入
11 as
12 declare @sql varchar(8000)
13
14 if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
15 begin
16 set @sql='bcp '+@tbname
17 +case when @isout=1 then ' out ' else ' in ' end
18 +' "'+@filename+'" /w'
19 +' /S '+@servername
20 +case when isnull(@username,'')='' then '' else ' /U '+@username end
21 +' /P '+isnull(@password,'')
22 exec master..xp_cmdshell @sql
23 end
24 else
25 begin --导出整个数据库,定义游标,取出所有的用户表
26 declare @m_tbname varchar(250)
27 if right(@filename,1)<>'\' set @filename=@filename+'\'
28
29 set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
30 exec(@m_tbname)
31 open #tb
32 fetch next from #tb into @m_tbname
33 while @@fetch_status=0
34 begin
35 set @sql='bcp '+@tbname+'..'+@m_tbname
36 +case when @isout=1 then ' out ' else ' in ' end
37 +' "'+@filename+@m_tbname+'.txt " /w'
38 +' /S '+@servername
39 +case when isnull(@username,'')='' then '' else ' /U '+@username end
40 +' /P '+isnull(@password,'')
41 exec master..xp_cmdshell @sql
42 fetch next from #tb into @m_tbname
43 end
44 close #tb
45 deallocate #tb
46 end
47 go
2 drop procedure File2Table
3 go
4 create procedure File2Table
5 @servername varchar(200) --服务器名
6 ,@username varchar(200) --用户名,如果用NT验证方式,则为空''
7 ,@password varchar(200) --密码
8 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
9 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
10 ,@isout bit --1为导出,0为导入
11 as
12 declare @sql varchar(8000)
13
14 if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
15 begin
16 set @sql='bcp '+@tbname
17 +case when @isout=1 then ' out ' else ' in ' end
18 +' "'+@filename+'" /w'
19 +' /S '+@servername
20 +case when isnull(@username,'')='' then '' else ' /U '+@username end
21 +' /P '+isnull(@password,'')
22 exec master..xp_cmdshell @sql
23 end
24 else
25 begin --导出整个数据库,定义游标,取出所有的用户表
26 declare @m_tbname varchar(250)
27 if right(@filename,1)<>'\' set @filename=@filename+'\'
28
29 set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
30 exec(@m_tbname)
31 open #tb
32 fetch next from #tb into @m_tbname
33 while @@fetch_status=0
34 begin
35 set @sql='bcp '+@tbname+'..'+@m_tbname
36 +case when @isout=1 then ' out ' else ' in ' end
37 +' "'+@filename+@m_tbname+'.txt " /w'
38 +' /S '+@servername
39 +case when isnull(@username,'')='' then '' else ' /U '+@username end
40 +' /P '+isnull(@password,'')
41 exec master..xp_cmdshell @sql
42 fetch next from #tb into @m_tbname
43 end
44 close #tb
45 deallocate #tb
46 end
47 go