生成表中的数据的脚本。

在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 = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,varbinary,bit' 
  declare @chartypes nvarchar(80) 
  select @chartypes = N'char,varchar,text,nchar,ntext,nvarchar' 
  declare @datetypes nvarchar(80) 
  select @datetypes = N'datetimesmalldatetime' 
  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，够用就行，懒得改了。

本文地址：http://com.8s8s.com/it/it19615.htm