Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

MERGE with ANSI Expand / Collapse
Author
Message
Posted Sunday, November 27, 2011 11:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
Tough one, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1212283
Posted Monday, November 28, 2011 12:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
A very tough question....


Post #1212292
Posted Monday, November 28, 2011 9:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 4,125, Visits: 3,425
I have unbfair advantage: MERGE is my daily bread.

Thanks for this interesting question!
Post #1212578
Posted Monday, November 28, 2011 9:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 17,710, Visits: 15,581
Thanks Hugo



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1212585
Posted Monday, November 28, 2011 4:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
thanks Hugo!
Post #1212808
Posted Tuesday, November 29, 2011 6:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Challenging question. Thanks for submitting.

http://brittcluff.blogspot.com/
Post #1213157
Posted Thursday, December 29, 2011 3:15 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:28 AM
Points: 629, Visits: 140
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'
Post #1228110
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse