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
Wednesday, 31 July 2013
Monday, 15 July 2013
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
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
Subscribe to:
Posts (Atom)