生成表中的数据的脚本。
在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，够用就行，懒得改了。