我们可以更新SQL中除一列之外的所有列吗

Question

我尝试了下面的代码，但它不起作用。

MERGE INTO Target as t
USING Source as s
ON s.Position = t.Position
WHEN MATCHED THEN
UPDATE SET * [except Created]
WHEN NOT MATCHED
THEN INSERT *

Answer 1

假设您在两个表中都有相同的列名，则应该使用以下方法：

DECLARE @SchemaName varchar(64),
@TargetTableName varchar(64),
@SourceTableName varchar(64),
@KeyColumn varchar(64),
@ExclusionColumn varchar(64),
@ColumnList varchar(1024),
@UpdateColumnList varchar(8000),
@SourceColumnList varchar(8000),
@Statement nvarchar(max)
SET @SchemaName='dbo'
SET @TargetTableName='Target'
SET @SourceTableName='Source'
SET @KeyColumn='Position'
SET @ExclusionColumn='Created'

SELECT @ColumnList = STUFF((
select  ',' + COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA=@SchemaName 
and TABLE_NAME=@TableName 
and COLUMNPROPERTY(OBJECT_ID(@SchemaName+'.'+@TableName),COLUMN_NAME,'isComputed')=0  
FOR XML PATH ('')),1,1,'')
SELECT @UpdateColumnList = STUFF((
select  ',' + COLUMN_NAME +'=source.'+COLUMN_NAME  
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA=@SchemaName 
and TABLE_NAME=@TableName 
and COLUMN_NAME not in (@ExclusionColumn,@KeyColumn)
and COLUMNPROPERTY(OBJECT_ID(@SchemaName+'.'+@TableName),COLUMN_NAME,'isComputed')=0  FOR XML PATH ('')),1,1,'')
SELECT @SourceColumnList = STUFF((
select  ',' + 'source.'+COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA=@SchemaName 
and TABLE_NAME=@TableName 
and COLUMNPROPERTY(OBJECT_ID(@SchemaName+'.'+@TableName),COLUMN_NAME,'isComputed')=0  FOR XML PATH ('')),1,1,'')

SET @Statement = 'MERGE ' + @SchemaName + '.' + @TargetTableName +  ' AS Target USING (SELECT ' +
@ColumnList + ' FROM  '+ @SchemaName +'.' + @SourceTableName +  ') AS source  ' +
' ON (target.'+@KeyColumn+'= source.'+@KeyColumn+')' +
' WHEN MATCHED THEN UPDATE SET '+ @UpdateColumnList +
' WHEN NOT MATCHED THEN INSERT('+@ColumnList+')
VALUES ('+@SourceColumnList+');'

EXECUTE sp_executesql @Statement