IDC资讯中心
在a服务器中有表 table1 ，想把该表的内容导到 b服务器中，这两个数据库没联网，怎么办？
导成外部文件，再用导入导出。。。。 这太笨了吧，如果对方服务器是internet上的，只能执行服本怎么办，晕死了吧，用这个存储过程来帮你。
alter proc sp_gettabledata
@objname nvarchar(776)
as
— set @objname=s_coderule
/* declare @no varchar(35), @yes varchar(35), @none varchar(35)
select @no = name from master.dbo.spt_values where type = b and number = 0
select @yes = name from master.dbo.spt_values where type = b and number = 1
select @none = name from master.dbo.spt_values where type = b and number = 2
*/
declare @numtypes nvarchar(80)
select @numtypes = ntinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,varbinary,bit
declare @chartypes nvarchar(80)
select @chartypes = nchar,varchar,text,nchar,ntext,nvarchar
declare @datetypes nvarchar(80)
select @datetypes = ndatetimesmalldatetime
declare @limitlength int, @limitlength1 int
declare @objid bigint
–declare @sysobj_type char(2)
select @objid = id
–, @sysobj_type = xtype
from sysobjects where id = object_id(@objname)
set @limitlength = 0
set @limitlength1 = 0
declare @name varchar(30),@type varchar(10),@length int,@fieldsql varchar(5000),@sql varchar(8000),@ident smallint
set @sql=
set @fieldsql=
declare cur cursor
for
select
column_name = name,
type = type_name(xusertype),
length = convert(int, length),
ident =colstat
from syscolumns where id = @objid and number = 0 order by colid
open cur
fetch next from cur into @name,@type,@length,@ident
while @@fetch_status=0
begin
if @ident=0
begin
if @limitlength > 200
begin
set @sql = @sql + ,
set @limitlength = 0
end
if @limitlength1 > 200
begin
set @fieldsql = @fieldsql + char(39) + , + char(39)
set @limitlength1 = 0
end
set @fieldsql = @fieldsql + ltrim(rtrim(@name)) + ,
if charindex(@type, @numtypes) >0–(+convert(char(6),@length)+)
set @sql=@sql++ case when +@name+ is null then null else rtrim(convert(char, + @name + )) end
else if charindex(@type, @chartypes) >0
set @sql=@sql++ case when +@name+ is null then null else +char(39) + rtrim( + @name + )+char(39) end
else if charindex(@type, @datetypes) >0
set @sql=@sql++ case when +@name+ is null then null else +char(39) + rtrim(convert(char(19),+@name+,120))+char(39) end
set @sql=@sql+++char(39)+,+char(39)
set @limitlength = @limitlength + @length
set @limitlength1 = @limitlength1 + len(@name)
end
fetch next from cur into @name,@type,@length,@ident
end
close cur
deallocate cur
— print @sql
if right(@fieldsql, 1) = char(39)
set @fieldsql = substring(@fieldsql, 1, len(@fieldsql)-5)
else
set @fieldsql = substring(@fieldsql, 1, len(@fieldsql)-1)
if right(@sql, 1) = ,
set @sql = substring(@sql, 1, len(@sql)-5)
else
set @sql=substring(@sql,1,len(@sql)-4)
–print(@fieldsql)
set @fieldsql = select +char(39)+insert into +@objname + ( + @fieldsql + )
–set @fieldsql = select +char(39)+insert into +@objname+(+ @fieldsql +)
print @fieldsql
set @sql = values(+char(39)+@sql+++char(39)+)+char(39)+ from +@objname
print @sql
set @sql = @fieldsql + @sql
exec(@sql)
–exec @fieldsql + @sql
–select rtrim(isnull(convert(char(8),did),)) from s_send_files
go
set quoted_identifier off
go
set ansi_nulls on
go
看看结果吧，有一些bug，够用就行，懒得改了。
