November 29, 2011 at 6:17 am
Challenging question. Thanks for submitting.
http://brittcluff.blogspot.com/
December 29, 2011 at 3:15 pm
My brain almost exploded..lol!! Pretty hard question. I actually have to open my book and read more about MERGE statement. I was under impression that this MERGE functionality came in SQL2008 only since I start using it in SQL2008 only. I didn't get lots of opportunities to play with newer functionality in 2005.
One thing I would like to point out is that MERGE statement does allows BY SOURCE and BY TARGET in the statement. Here's the snippet of code where I use both:
MERGE dbo.column_collection AS cc
USING dbo.staging_column_collection AS scc
ON (
scc.table_name = cc.table_name
AND scc.database_name = cc.database_name
AND scc.ordinal_position = cc.ordinal_position
AND scc.record_status = cc.record_status
AND scc.schema_name = cc.schema_name
AND cc.record_status = 'A'
AND scc.platform_type_code = cc.platform_type_code
AND scc.database_name NOT LIKE '_broken%'
)
WHEN MATCHED AND cc.physical_column_name <> scc.physical_column_name
OR cc.data_type <> scc.data_type
OR cc.column_length <> scc.column_length
THEN UPDATE
SET cc.physical_column_name = scc.physical_column_name
, cc.column_name = scc.column_name
, cc.data_type = scc.data_type
, cc.column_length = scc.column_length
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
column_name
, physical_column_name
, data_type
, column_length
, column_definition
, record_status, insert_date
, table_name, database_name
, ordinal_position
, schema_name
, platform_type_code
)
VALUES
(
scc.column_name
, scc.physical_column_name
, scc.data_type
, scc.column_length
, scc.column_definition
, scc.record_status
, scc.insert_date
, scc.table_name
, scc.database_name
, scc.ordinal_position
, schema_name
, platform_type_code
)
WHEN NOT MATCHED BY SOURCE
THEN UPDATE
SET cc.record_status = 'D'
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply