SQLServer实例之间克隆表

场景： 两个SQLServer实例（可能在不同机器上）

（1）实例1 ：　SarahCla

（2）实例2：???? CloneServer

目标：将SarahCla中的某些表的结构及数据克隆到CloneServer中

step 1： 在CloneServer中建立SarahCla的linkserver，执行语句如下

Exec sp_droplinkedsrvlogin [SARAHCLA],Null

EXEC? sp_addlinkedserver ?
????? @server='SARAHCLA',
????? @srvproduct='',?
????? @provider='SQLOLEDB',?
????? @datasrc="SarahCla"? ?
?????
EXEC sp_addlinkedsrvlogin? ?
???? 'SARAHCLA',
???? 'false',? ?
???? NULL,? ?
???? 'sa',--帐号 ?
???? 'XXXXX' --密码???? 嚯嚯，差点忘了删密码

step 2: 在SarahCla 实例中相应的数据库中创建存储过程，获取table的创建语句。

（linkserver直接select * into 会丢掉一些列的property，例如identity）

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128),????? ?
@sql nvarchar(4000) output,
@cols nvarchar(4000) output
as

declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID?? TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length?? TinyInt
declare @Prec???? TinyInt
declare @Scale??? TinyInt
declare @Status?? TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID???? SmallInt ?
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName??? varchar(30)
declare @strPri_Key varchar (255)

if not Exists(Select name from sysobjects where name = @ObjName)
begin
? select @DBName = db_name()
??? raiserror(15009,-1,@ObjName,@DBName)
??? return (1)
end

create table #spscript
(
??? id???? int IDENTITY not null,
??? Script Varchar(255) NOT NULL,
??? LastLine tinyint
)

declare Cursor_Column INSENSITIVE CURSOR
? for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status,a.cDefault,
??????? case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
??????? from syscolumns a,systypes b where object_name(a.id) = @ObjName
??????? and a.usertype = b.usertype order by a.ColID

set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)

/* Get column information */
open Cursor_Column

fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
????? @Status,@cDefault,@Const_Key

Select @Script = ''
while (@@FETCH_STATUS <> -1)
begin
? if (@@FETCH_STATUS <> -2)
? begin

?? ?if (@cols='')
?? ??? ?set @cols = @ColName
?? ?else
?? ??? ?set @cols = @cols + ',' + @Colname
?? ??? ?
??? Select @Script = @ColName + ' ' + @TypeName
?? ?
??? if @UserType in (1,2,3,4)
????? Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
??? else if @UserType in (24)
????? Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
????????????????????? + Convert(char(3),@Scale) + ') '
??? else
????? Select @Script = @Script + ' '
??? if ( @Status & 0x80 ) > 0
????? Select @Script = @Script + ' IDENTITY(1,1) '

??? if ( @Status & 0x08 ) > 0
????? Select @Script = @Script + ' NULL '
??? else
????? Select @Script = @Script + ' NOT NULL '
??? if @cDefault > 0
????? Select @Script = @Script + ' DEFAULT ' + @Const_Key
? end
? fetch next from Cursor_Column into @ColName,@Status,@Const_Key
? if @@FETCH_STATUS = 0
? begin
??? Select @Script = @Script + ','
??? Insert into #spscript values(@Script,0)
? end
? else
? begin
??? Insert into #spscript values(@Script,1)
??? Insert into #spscript values(')',0)
? end
end
Close Cursor_Column
Deallocate Cursor_Column

/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR for
?? ?Select name,IndID,status
?? ?from sysindexes
?? ?where object_name(id)=@ObjName and IndID > 0 and IndID<>255 ?
?? ?order by IndID? ?
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName,@IndID,@IndStatus
while (@@FETCH_STATUS <> -1)
begin
? if @@FETCH_STATUS <> -2
? begin

??? declare @i TinyInt
??? declare @thiskey varchar(50)
??? declare @IndDesc varchar(68)

??? Select? @i = 1
??? while (@i <= 16)
??? begin
????? select @thiskey = index_col(@ObjName,@i)
????? if @thiskey is null
??????? break

????? if @i = 1
??????? select @Index_Key = index_col(@ObjName,@i)
????? else
??????? select @Index_Key = @Index_Key + ',' + index_col(@ObjName,@i)
????? select @i = @i + 1
??? end
??? if (@IndStatus & 0x02) > 0
????? Select @Script = 'Create unique '
??? else
????? Select @Script = 'Create '
??? if @IndID = 1
????? select @Script = @Script + ' clustered '


??? if (@IndStatus & 0x800) > 0
???? select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
??? else
???? select @strPri_Key = ''
??? ?
??? if @IndID > 1
????? select @Script = @Script + ' nonclustered '
??? Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
?????????? + '(' + @Index_Key + ')'
??? Select @IndDesc = ''
??? if @IndStatus & 0x01 = 0x01
????? Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
??? if @IndStatus & 0x40 = 0x40
????? Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
??? if @IndDesc <> ''
??? begin
????? Select @IndDesc = SubString( @IndDesc,1,DataLength(@IndDesc) - 1 )
????? Select @Script = @Script + ' WITH ' + @IndDesc
??? end
? end
? if (@strPri_Key = '')
??? Insert into #spscript values(@Script,0)
? else
??? update #spscript set Script = Script + @strPri_Key where LastLine = 1
?
? Fetch Next from Cursor_Index into @ColName,@IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index

declare @line varchar(8000)
set @sql = ''
Declare Cursor_sql CURSOR for Select script from #spscript
Open Cursor_sql
Fetch Next from Cursor_sql into @line
while (@@FETCH_STATUS <> -1)
begin
?? ?set @sql = @sql + ' ' + @line
?? ?Fetch Next from Cursor_sql into @line
end
Close Cursor_sql
Deallocate Cursor_sql



set nocount off

return (0)

step 3: 在CloneServer 上创建执行克隆任务的存储过程

create procedure tableClone
@table nvarchar(1000)
As
begin
?? ?declare @sql nvarchar(4000);
?? ?declare @cols nvarchar(4000);
?? ?set @cols=''
?? ?execute [SarahCla].master.dbo.SP_GET_TABLE_INFO @table,@sql output,@cols output;

??? --SarahCla记得改成自己的linkserver name

??? --master 改成自己要克隆的表所在库的名称

?? ?execute sp_executesql @sql;
?? ?set @sql = ' set identity_insert tb3 on; insert into ' + @table + '(' + @cols + ')
?? ?select ' + @cols + ' From [SarahClaSarahcla2008].master.dbo.' + @table + ' set identity_insert ' + @table + ' off '
?? ?execute sp_executesql @sql;
end

step 4: 克隆表

例如：要克隆 tb3

execute tableClone 'tb3'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!