Я, к примеру, написал для этого процедуру. Копируются данные
одноименных таблиц из другой базы и сервера(который должен быть прилинкован).


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE PROCEDURE SP_CopyDataFromDB
-- Копирование данных таблиц(ы) из другой БД, не/с локального сервера.
@DbsName sysname, -- Имя: БД
@TabName sysname=NULL, -- таблицы | NULL - все
@SrvName sysname=NULL, -- сервера | NULL - локальный
@OwnName sysname=NULL, -- владельца
@PrShow tinyint=1 -- Флаг: > 0 - с выдачей протокола
AS -- Ошибки: 1 - неверный параметр
set nocount on -- 4 - операция не выполнена
-- 0000000000000000000000000000000000000000000000000000000000000000000000000000
declare @NamC sysname, @NamT sysname, @TyC varchar(20), @LCT varchar(6000),
@Coll sysname, @IdT int, @I int, @ExT varchar(1000)
declare @CouRows table ([Table] [sysname] collate database_default,[Rows] [int])
-- ............................................................................
if not exists(select '*' from sysobjects where xtype = 'U' and
status >= 0 and OBJECTPROPERTY(id,N'IsUserTable') = 1 and
name = isnull(@TabName,name)) begin
select @LCT='The table'+' '''+DB_Name()+'..'+@TabName+''' - is unknown!'
raiserror (@LCT,13,3) return 1 end
if @SrvName is NULL begin
if not exists(select '*' from master.dbo.sysdatabases where name = @DbsName)
begin select @LCT='The DB'+' '''+@DbsName+''' - is unknown!'
raiserror (@LCT,13,3) return 1 end end
else begin
if not exists(select '*' from master.dbo.sysservers where srvname = @SrvName)
begin select @LCT='The server'+' '''+@SrvName+''' - is not linked!'
raiserror (@LCT,13,3) return 1 end
select @LCT='declare @x int select @x=1 from '+@SrvName+
'.master.dbo.sysdatabases where name='''+@DbsName+''''
exec(@LCT) if @@ROWCOUNT = 0 begin
select @LCT='The DB'+' '''+@SrvName+'.'+@DbsName+''' - is unknown!'
raiserror (@LCT,13,3) return 1 end end
select @ExT='declare @x int select @x=1 from '+
case when @SrvName is NULL then '' else @SrvName+'.' end+@DbsName+
'.dbo.sysobjects where xtype = ''U'' and status >= 0 and name = ''',
@Coll=convert(sysname,DatabasePropertyEx(DB_name(),'Collation'))
-- TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
declare cur_SelCopTabDB cursor local for select name,ID from sysobjects
where xtype = 'U' and status >= 0 and OBJECTPROPERTY(id,N'IsUserTable') = 1
and name = isnull(@TabName,name) order by name
open cur_SelCopTabDB
fetch next from cur_SelCopTabDB into @NamT,@IdT
if @@ERROR != 0 begin
raiserror ('The data - is not copied!',13,3) return 4 end
while (@@FETCH_STATUS != -1) begin
if (@@FETCH_STATUS != -2) begin select @LCT=''
exec(@ExT+@NamT+'''') if @@ROWCOUNT = 0 begin
insert into @CouRows values(@NamT,-1) end else begin
-- CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
declare cur_SelCopColDB cursor local for select name,type_name(xusertype)
from syscolumns where id = @IdT and number = 0
and ColumnProperty(@IdT,name,'IsIdentity') = 0 order by colid
select @I=count(*) from syscolumns where id = @IdT and number = 0
open cur_SelCopColDB
while @I > 0 begin select @I=@I-1
fetch next from cur_SelCopColDB into @NamC,@TyC
if @@ERROR != 0 begin
raiserror ('The data - is not copied!',13,3) return 4 end
if @TyC not in ('text','ntext','image','uniqueidentifier')
select @LCT=@LCT+@NamC+case when @TyC in ('char','nchar','varchar','nvarchar')
then ' collate '+@Coll else '' end+',' end
select @I=datalength(@LCT)-1,@LCT=substring(@LCT,1,@I),
@LCT='insert into '+@NamT+' ('+replace(@LCT,' collate '+@Coll,'')+
') select '+@LCT+' from '+case when @SrvName is NULL then ''
else @SrvName+'.' end+@DbsName+'.'+
isnull(@OwnName,'dbo')+'.'+@NamT
exec(@LCT) select @IdT=@@ERROR,@I=@@ROWCOUNT
if @IdT != 0 begin
raiserror ('The data - is not copied!',13,3) return 4 end
insert into @CouRows values(@NamT,@I)
close cur_SelCopColDB deallocate cur_SelCopColDB end end
fetch next from cur_SelCopTabDB into @NamT,@IdT
if @@ERROR != 0 begin
raiserror ('The data - is not copied!',13,3) return 4 end end
close cur_SelCopTabDB deallocate cur_SelCopTabDB
-- ============================================================================
if isnull(@PrShow,0) > 0 select * from @CouRows return 0
-- #### (c) "PolySystem" 28.03.02 ##########################################







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO