** 本文属spanzhang原创，其blog地址为： ** ** http://blog.csdn.net/spanzhang ** ** 。引用或转贴请注明出处，谢谢！！ **
/*
作者：张友邦
时间：2005-03-03
描述：简单的拷贝一个表的内容
版本：1.0
历史：
注意：
*/
ALTER procedure dbo.x_p_copyTable
(
@tableName nvarchar ( 128 ),
@condition nvarchar ( 1024 ) = N '' ,
@remoteServer nvarchar ( 128 ) = N ' TGM_OLD.span_tgm_020 '
)
as
declare @sql nvarchar ( 4000 )
set @sql = N ' select @cnt = count(*)
from sysobjects, syscolumns
where sysobjects.id = syscolumns.id
and syscolumns.colstat = 1
and sysobjects.name = ''' + @tableName + N ''''
declare @count int
execute sp_executesql @sql, N ' @cnt int output ' , @count output
if @count > 0 begin
set @sql = N ' set identity_insert ' + @tableName + N ' on '
execute sp_executesql @sql
end
declare @columns nvarchar ( 2048 )
set @columns = N ''
select
@columns = @columns + syscolumns.name + N ' , '
from sysobjects, syscolumns
where sysobjects.id = syscolumns.id
and syscolumns.iscomputed <> 1
and sysobjects.name = @tableName
if len (@columns) > 0 begin
set @columns = substring (@columns, 1 , len (@columns) - 1 )
end
set @sql = N ' insert ' + @tableName + N ' ( ' + @columns + N ' ) '
+ N ' select ' + @columns + N ' from ' + @remoteServer + N ' .dbo. ' + @tableName
if @condition <> N '' begin
set @sql = @sql + N ' where ' + @condition
end
execute sp_executesql @sql
if @count > 0 begin
set @sql = N ' set identity_insert ' + @tableName + N ' off '
execute sp_executesql @sql
end