自動排除計算字段拷貝表數據
2024-07-21 02:05:54
供稿：網友
本文屬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')asdeclare @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 intexecute sp_executesql @sql, n'@cnt int output', @count outputif @count > 0 begin set @sql = n'set identity_insert ' + @tablename + n' on' execute sp_executesql @sqlenddeclare @columns nvarchar(2048)set @columns = n''select @columns = @columns + syscolumns.name + n','from sysobjects, syscolumnswhere sysobjects.id = syscolumns.id and syscolumns.iscomputed <> 1 and sysobjects.name = @tablenameif len(@columns) > 0 begin set @columns = substring(@columns, 1, len(@columns) - 1)endset @sql = n'insert ' + @tablename + n'(' + @columns + n') ' + n'select ' + @columns + n' from ' + @remoteserver + n'.dbo.' + @tablenameif @condition <> n'' begin set @sql = @sql + n' where ' + @conditionendexecute sp_executesql @sqlif @count > 0 begin set @sql = n'set identity_insert ' + @tablename + n' off' execute sp_executesql @sqlend
