Dynamicallly writing those pesky Update Statements

Whenever you are loading a data warehouse, often one of the most time consuming tasks is writing the update statements that you need in order to properly handle changing records coming from your system. Further adding to development time, columns are then split into type 1 or type 2 attributes, and in my current shop, processes for deletes out of the source system as well. After you write a few of these, that loving feeling you got when you started starts to dissipate, and you start to feel bored with the same statements. So with that in mind, I wrote the below procedure that takes a table name and an update_type variable of U, V, or D and then generates the appropriate update, type 2 version, or delete statement you need. The procedure makes 2 assumptions, one in that you have a separate etl_working database to handle set based updates, and the second that your table primary keys contain of column named “version_key” as a part of the naming convention for your tables primary key. If this isn’t your naming convention, feel free to change it.

UPDATE: 01/21/2015: found a bug in the version code, fixed. Sorry about that!

/*
Author: Christopher Schmidt
Date: 2015-01-19
Description: taking the table name and an update type of U, V, or D as inputs, generates an update statement against the target table. This proc makes an assumption that
there is a separate database titled “etl_working”, that consists of a mirror copy of the target table to handle updates in a set based manner. This can be adjusted in the
code below on lines 55,58,65, and 67.

It also assumes that your primary_key of the target table contains the words
“version_key”. if you do not use this naming convention, you can update them in the where clauses of the column selects on lines 34,40,46, and 52.

As always, with any code you find on the internet, test and use in development.

*/

CREATE PROCEDURE [dbo].[update_generator](
@tbl varchar(200)
,@update_type char(1)
)
AS

DECLARE @sqlu varchar(5000)
DECLARE @sqld varchar(5000)
DECLARE @sqlv varchar(5000)
DECLARE @cols varchar(4000)
DECLARE @colsi varchar(4000)
DECLARE @colsv varchar(4000)
DECLARE @keycol varchar(200)
DECLARE @keycold varchar(200)

set @cols = stuff(( select ‘,’+quotename(C.name) + ‘=Src.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name NOT LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @colsi = stuff(( select ‘,’+quotename(C.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name NOT LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @keycol = stuff(( select ‘,’+quotename(C.name) + ‘=Src.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @keycold = stuff(( select ‘,src.’+quotename(C.name) + ‘= dw.’ + quotename(c.name)
from sys.columns as C
where (C.object_id = object_id(@tbl)
AND c.name LIKE ‘%version_key’)
for xml path(”)), 1, 1, ”)

SET @sqlu = N’UPDATE [‘ + @tbl + ‘] SET ‘ + @cols + ‘ FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”U”’

SET @sqld = N’UPDATE [‘ + @tbl + ‘] SET dmproc_batch_key=src.dmproc_batch_key, source_revision_action = ”D”, version_end_dtmx = GETDATE(), version_current_ind = 0
FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”D”’

SET @sqlv = N’UPDATE ‘ + @tbl + ‘ SET version_current_ind = 0
,version_latest_ind = 0
,dmproc_batch_key=src.dmproc_batch_key
,version_end_dtmx=GETDATE()
,source_revision_end_dtmx = GETDATE()
,source_revision_action = ”V” FROM change.[‘ + @tbl + ‘] [src] JOIN dbo.[‘ + @tbl + ‘] [dst] ON dst.’ + @keycol + ‘ WHERE Update_Type = ”V”
GO
INSERT INTO dbo.[‘ + @tbl + ‘] ( ‘ + @colsi + ‘) SELECT ‘ + @colsi + ‘FROM change.[‘ + @tbl + ‘] WHERE Update_Type = ”V”’

SELECT
CASE
WHEN @update_type = ‘U’ THEN @sqlu
WHEN @update_type = ‘D’ THEN @sqld
WHEN @update_type = ‘V’ THEN @sqlv
END [sql]

 

 

Hope it saves you some time!