本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
在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 @[email protected]+'+ case when '[email protected]+' is null then ''null'' else rtrim(convert(char,' + @name + ')) end'
else if charindex(@type, @chartypes) >0
set @[email protected]+'+ case when '[email protected]+' is null then ''null'' else +char(39) + rtrim(' + @name + ')+char(39) end '
else if charindex(@type, @datetypes) >0
set @[email protected]+'+ case when '[email protected]+' is null then ''null'' else +char(39) + rtrim(convert(char(19),'[email protected]+',120))+char(39) end'
set @[email protected]+'+'+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 '[email protected] + '(' + @fieldsql + ')'
--set @fieldsql = 'select '+char(39)+'insert into '[email protected]+'('+ @fieldsql +')'
print @fieldsql
set @sql = ' values('+char(39)[email protected]+'+'+char(39)+')'+char(39)+' from '[email protected]
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，夠用就行，懶得改了。
