January 8, 2013 at 8:59 am
I have this generated ETL code that I need to tweak a bit so it works when run. The main problem right now is that I have two WHEN MATCHED statements. I need to keep both of these because it is essential to the ETL. Here is the code below. Any clue how to have these both work?
MERGE DBA_ADS_Programability_Dim AS Target
USING (
SELECT
object_id SrcSys_KEY,
name Programabilty_NME,
type_desc Programability_TYP
FROM sys.all_objects
WHERE type in ('TF', 'FN', 'AF', 'P')
AND is_ms_shipped = 0
) AS Source
ON (Source.SrcSys_KEY = Target.SrcSys_Key)
WHEN MATCHED and
COALESCE('target.[Programability_TYP] <> source.[Programability_TYP] collate SQL_Latin1_General_CP1_CI_AS', '') <> '' and
(target.[Programability_TYP] <> source.[Programability_TYP] collate SQL_Latin1_General_CP1_CI_AS)
THEN
UPDATE SET
target.[Programability_TYP] = source.[Programability_TYP]
WHEN MATCHED and
COALESCE('target.[Programabilty_NME] <> source.[Programabilty_NME]', '') <> '' and
(target.[Programabilty_NME] <> source.[Programabilty_NME])
THEN
UPDATE
SET Expiration_DTM = '2013-01-06 23:59:59.977'
WHERE Expiration_DTM = '12/31/9999'
INSERT (
Effective_DTM,
Expiration_DTM,
SrcSys_KEY,
Programabilty_NME,
Programability_TYP
)
VALUES (
'2013-01-07 00:00:00.000',
'12/31/9999',
Source.SrcSys_KEY,
Source.Programabilty_NME,
Source.Programability_TYP
)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
Effective_DTM,
Expiration_DTM,
SrcSys_KEY,
Programabilty_NME,
Programability_TYP
)
VALUES (
'2013-01-07 00:00:00.000',
'12/31/9999',
Source.SrcSys_KEY,
Source.Programabilty_NME,
Source.Programability_TYP
)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE
SET Expiration_DTM = '2013-01-06 23:59:59.977'
;
January 8, 2013 at 9:17 am
I don't get this part:
COALESCE('target.[Programability_TYP] <> source.[Programability_TYP] collate SQL_Latin1_General_CP1_CI_AS', '')
Why use a coalesce check on a string literal? There's no possible way for 'target.[Programability_TYP] <> source.[Programability_TYP] collate SQL_Latin1_General_CP1_CI_AS' to return a NULL. It's a string with content. Heck, could be a zero-length string and it would still be non-NULL.
Is this something that was written by another piece of code? Dynamic SQL of some sort? I've seen that kind of thing happen.
Merge in T-SQL can't do what you need.
Per MSDN (http://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx):
The MERGE statement can have at most two WHEN MATCHED clauses. If two clauses are specified, then the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first is not. If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action. If UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source>matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. The MERGE statement cannot update the same row more than once, or update and delete the same row.
(emphasis added)
You want two Update actions.
You'll need two separate Merge statements, or two separate Update From statements. I'd go with Merge, but that's a preference, not a necessity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:19 am
Yeah this is generated script so that's why I have the coalesce in there. I'll look more into the stuff you posted and see what I can do.
January 8, 2013 at 9:28 am
That makes sense.
Looked at a few more details.
This part isn't correct T-SQL syntax:
WHEN MATCHED and
COALESCE('target.[Programabilty_NME] <> source.[Programabilty_NME]', '') <> '' and
(target.[Programabilty_NME] <> source.[Programabilty_NME])
THEN
UPDATE
SET Expiration_DTM = '2013-01-06 23:59:59.977'
WHERE Expiration_DTM = '12/31/9999'
INSERT (
Effective_DTM,
Expiration_DTM,
SrcSys_KEY,
Programabilty_NME,
Programability_TYP
)
VALUES (
'2013-01-07 00:00:00.000',
'12/31/9999',
Source.SrcSys_KEY,
Source.Programabilty_NME,
Source.Programability_TYP
)
Merge can't do both an update and an insert off of one check. It can only do one action at a time.
This will definitely need to be broken down into multiple steps.
If the Source data is subject to change between steps, and if that would matter, then either need a single script with a single transaction (explicit) and Serializable Reads isolation level, or dump the source data into a temp table and then use that in your multiple Merge statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:35 am
Yeah that was throwing a fit too and I had no clue why. My main problem is that this is way over my head and this is kind of a sink or swim project at my first job. I have another DBA as a resource but he is kind of stumped by this and/or he wants me to figure it out on my own.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply