今天同事和同事讨论一个问题:她在日常工作中需要更新表数据,场景如此下:表A和表A_bak,两个表的结构一致,表A中的数据需根据表A_bak中的数据进行更新或插入的操作。这里我们不采用Merge方式,不能满足她的需求。在更新的时候由A表的列特别多,好几十列,同事问有什么好的办法能生成这个Update语句吗?免去一列一列写的痛苦。当时有点忙,就只给出了我的想法,用Update From 语句,动态的去构造这个Update。下午忙完手边的活,就想自己动手写下,锻炼哈。我给出我写的SQL:
数据结构如下:
DECLARE @TableName VARCHAR(50),@TableNameBAk VARCHAR(50),@rowCount INT,@index INT,@col VARCHAR(200),@pkcol VARCHAR(200),@cols VARCHAR(max); DECLARE @t TABLE(id INT IDENTITY,colName VARCHAR(50)); SELECT @TableName=N'Customers',@TableNameBAk=N'Customers_bak' ,@index=1; INSERT INTO @t SELECT NAME FROM syscolumns WHERE id=object_id(@TableName) SET @rowCount=@@ROWCOUNT WHILE @index<@rowCount BEGIN select @col=colName from @t where id=@index SET @index=@index+1 IF EXISTS(SELECT 1 FROM syscolumns JOIN sysindexkeys ON syscolumns.id=sysindexkeys.id AND syscolumns.colid=sysindexkeys.colid JOIN sysindexes ON syscolumns.id=sysindexes.id AND sysindexkeys.indid=sysindexes.indid JOIN sysobjects ON sysindexes.name=sysobjects.name AND sysobjects.xtype='PK' WHERE syscolumns.name=@col AND syscolumns.id=object_id(@TableName)) BEGIN SET @pkcol= @col CONTINUE; END SET @cols=ISNULL(@cols+',','')+@col+N'=a.'+@col; END SET @cols=N'Update '+@TableName+N' Set '+@cols +N' from ( select * from '+@TableNameBAk+' ) a where '+@TableName+'.'+@pkcol+'=a.'+@pkcol+'' PRINT @cols
注意,表要有主键