A friend of mine(SB) once said “I work hard at working smart”. It is a great motto to work by especially when working in a technical role. So in the spirit of this motto, I decided to find a smarter way to do a quick replace for a MERGE statement’s MATCHED clause.
So here is our scenario: I’ve got a table with about 200 columns in it. I need to update about 180 of them from my staging table to my dimension table. When I script out an update statement for my table and put the column list in my MERGE statement I get something like this:
WHEN MATCHED THEN
UPDATE SET
[RegistrationDate] = <RegistrationDate, int,>
,[RegistrationDateKey] = <RegistrationDateKey, int,>
,[CreationDate] = <CreationDate, datetime,>
,[CreationDateKey] = <CreationDateKey, int,>
,[FirstName] = <FirstName, varchar(100),>
,[MiddleName] = <MiddleName, varchar(100),>
,[LastName] = <LastName, varchar(100),>
,[AddressLine1] = <AddressLine1, varchar(100),>
,[AddressLine2] = <AddressLine2, varchar(100),>
,[City] = <City, varchar(100),>
,[State] = <State, varchar(5),>
,[PostalCode] = <PostalCode, varchar(100),>
,[EMail] = <EMail, varchar(200),>
,[PhoneNumber] = <PhoneNumber, varchar(100),>
,[CustomDateTimeValueLastChangedDateTime] =
<CustomDateTimeValueLastChangedDateTime, datetime,>
,[CustomDateTimeName1] = <CustomDateTimeName1, varchar(100),>
,[CustomDateTimeValue1] = <CustomDateTimeValue1, datetime,>
,[CustomDateTimeName2] = <CustomDateTimeName2, varchar(100),>
,[CustomDateTimeValue2] = <CustomDateTimeValue2, datetime,>
,[CustomDateTimeName3] = <CustomDateTimeName3, varchar(100),>
,[CustomDateTimeValue3] = <CustomDateTimeValue3, datetime,>
,[CustomDateTimeName4] = <CustomDateTimeName4, varchar(100),>
,[CustomDateTimeValue4] = <CustomDateTimeValue4, datetime,>
,[CustomDateTimeName5] = <CustomDateTimeName5, varchar(100),>
......
Only I need it to look like this:
WHEN MATCHED THEN
UPDATE SET
[RegistrationDate] = Source.[RegistrationDate]
,[RegistrationDateKey] = Source.[RegistrationDateKey]
,[CreationDate] = Source.[CreationDate]
,[CreationDateKey] = Source.[CreationDateKey]
,[FirstName] = Source.[FirstName]
,[MiddleName] = Source.[MiddleName]
,[LastName] = Source.[LastName]
,[AddressLine1] = Source.[AddressLine1]
,[AddressLine2] = Source.[AddressLine2]
,[City] = Source.[City]
,[State] = Source.[State]
,[PostalCode] = Source.[PostalCode]
,[EMail] = Source.[EMail]
,[PhoneNumber] = Source.[PhoneNumber]
,[CustomDateTimeValueLastChangedDateTime] = Source.[CustomDateTimeValueLastChangedDateTime]
,[CustomDateTimeName1] = Source.[CustomDateTimeName1]
,[CustomDateTimeValue1] = Source.[CustomDateTimeValue1]
,[CustomDateTimeName2] = Source.[CustomDateTimeName2]
,[CustomDateTimeValue2] = Source.[CustomDateTimeValue2]
,[CustomDateTimeName3] = Source.[CustomDateTimeName3]
,[CustomDateTimeValue3] = Source.[CustomDateTimeValue3]
,[CustomDateTimeName4] = Source.[CustomDateTimeName4]
,[CustomDateTimeValue4] = Source.[CustomDateTimeValue4]
,[CustomDateTimeName5] = Source.[CustomDateTimeName5]
.....
I’ve been using regular expressions for a long time, but I recently discovered a little known feature called replacement expressions. Basically, it allows the replace to use a portion of what it found and put it back. This is great news!
So here is what I came up with:
In my Find What box I use the following: {\[[:a]+\]} = \<[:a, ()]+\>
In my Replace with box I use the following: \1 = Source.\1
I run my replace all and I instantly have the columns with the syntax I need. I hope this comes in handy for you; I know it sure came in handy for me.
