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
No comments:
Post a Comment