自动排除计算字段拷贝表数据
自动排除计算字段拷贝表数据
自动排除计算字段拷贝表数据 本文属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
