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

The Beginning

Dear all,


This is the initial post for my BI 360 blog site.



My Current blog project is still in draft, the Subject is dynamic SSAS Partitioning and processing using SSIS




It is due for release in two weeks time.


Cheers,



Andreas Bergstedt