How to quickly generate that Update statement in TSQL

OK so we all hate writing Update and Merge statements....

They all seem to take so much time..


Here is a bit of TSQL that will ease your pain.

The Code implies that you have a Schema called "Stage" and that your target table is running under the "dbo" schema(schema_id=1).


Here Is the Code:

--Start TSQL


--Table to Insert Into
Declare @@TBL as VARCHAR(100) = 'DimProductCategory'
--General Declarations
Declare @@TT as Table (IDX INT,TName varchar(100),CName Varchar(100))
Declare @@EXEC as VARCHAR(MAX)
Declare @@Cnames as Varchar(MAX)
Declare @@IDX as Int = 1

--Build Column List
Insert Into @@TT
Select Row_Number() Over ( Order By t.name )as IDX,t.name as TName,C.Name as CName
from Sys.columns c
inner join sys.Tables t on c.object_id=t.object_id
where t.name = @@TBL and c.name != Replace(@@TBL,'Dim','')+'ID'
and c.is_computed=0 and t.schema_id=1

--Generate TSQL
While @@IDX <= (Select MAX(IDX) From @@TT)

Begin
Declare @CLM as Varchar(100) = (Select Cname from @@TT Where IDX=@@IDX)
if @@IDX=1 SET @@Cnames ='UPDATE dbo.'+@@TBL+' SET
'+@CLM+' = bpSrc.'+@CLM
if @@IDX>1 SET @@Cnames = @@Cnames +'
, '+@CLM+'= bpSrc.'+@CLM + ' '

Set @@IDX = @@IDX+1
End


Set @@EXEC = @@Cnames + '
FROM [dbo].'+@@TBL+' bp
INNER JOIN stage.'+@@TBL+' bpSrc
on bp.BusinessKeyHash = bpSrc.BusinessKeyHash
AND bp.'+Replace(@@TBL,'Dim','')+'SourceKey = bpSrc.'+Replace(@@TBL,'Dim','')+'SourceKey '

--Print out the TSQL
Print @@EXEC
GO

--End TSQL



Happy Hunting