Merge Targets

Question

Merge Targets

Steve Jones - SSC Editor

SSC Guru

Points: 734434
More actions
September 8, 2014 at 8:34 pm

#74257

Comments posted to this topic are about the item Merge Targets

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

saravanan.a 88885 SSC Eights! Points: 993 More actions · Answer 1

Hi Steve Jones,

i have tried the Stairway's Example,its updating and inserting in Sales table only am i going wrong any where?. The script i used as follows.

drop table Sales

CREATE TABLE dbo.Sales (Id int,

SalesAmount money);

INSERT INTO dbo.Sales VALUES(1,10.99);

-- Create Source Table

drop table NewSalesNAdjustments

CREATE TABLE dbo.NewSalesNAdjustments(New_Id int,

New_SalesAmount money);

INSERT INTO dbo.NewSalesNAdjustments VALUES (1, 12.99);

INSERT INTO dbo.NewSalesNAdjustments VALUES (2, 5.99);

MERGE dbo.Sales

USING dbo.NewSalesNAdjustments

ON New_Id = Id

WHEN MATCHED THEN -- Update

UPDATE SET SalesAmount = New_SalesAmount

WHEN NOT MATCHED THEN -- Insert

INSERT (Id,SalesAmount) VALUES (New_Id,New_SalesAmount);

select * from Sales

select * from NewSalesNAdjustments

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Thanks for the question Steve but oooops:crazy:, please correct the answer!

😎

happycat59 One Orange Chip Points: 29387 More actions · Answer 3

Mmm, I selected Sales and was told it was the wrong answer.

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 4

MERGE dbo.Sales
USING dbo.Salesdjustments
ON New_Id = Id
WHEN MATCHED THEN -- Update
UPDATE SET SalesAmount = NewSalesAmount
WHEN NOT MATCHED THEN -- Insert
INSERT (Id,SalesAmount) VALUES (New_Id,NewSalesAmount);[/B]

Hehehe i think there is no table Salesadjustments,

Answer should be sales. Please correct the question.

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 5

Eirikur Eiriksson (9/8/2014)
Thanks for the question Steve but oooops:crazy:, please correct the answer!
😎

Louis Hillebrand SSCarpal Tunnel Points: 4970 More actions · Answer 6

Updating the source of the merge ??, please correct the answer !

Carlo Romagnano SSC-Insane Points: 22599 More actions · Answer 7

From BOL:

MERGE

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

USING <table_source>

ON <merge_search_condition>

[ WHEN MATCHED [ AND <clause_search_condition> ]

THEN <merge_matched> ] [ ...n ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

THEN <merge_not_matched> ]

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

THEN <merge_matched> ] [ ...n ]

[ <output_clause> ]

[ OPTION ( <query_hint> [ ,...n ] ) ]

;

target_table
Is the table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

Hany Helmy SSChampion Points: 13921 More actions · Answer 8

According to BOL:

USING <table_source>

Specifies the data source that is matched with the data rows in target_table.

So in the question, table SalesAdjustments is the Source table not the target, I want my point back 😛

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

Louis Hillebrand (9/9/2014)
Updating the source of the merge ??, please correct the answer !

Another wrong answer 😎

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Uwe Ricken Hall of Fame Points: 3142 More actions · Answer 10

Eirikur Eiriksson (9/8/2014)
Thanks for the question Steve but oooops:crazy:, please correct the answer!
😎

+1

I thought beeing in the wrong theater 🙂

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

robertjtjones SSC-Addicted Points: 484 More actions · Answer 11

Hmmm - the SalesAdjustments table doesn't exist in the statement 😛

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

happycat59 (9/8/2014)
Mmm, I selected Sales and was told it was the wrong answer.

Same here buddy & 93% did the same :w00t:

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 13

Agree, that I want my point back.

The only thing I can add is that Either should be an option given that the column definitions actually differ! Thus if it is possible to have updated SalesAdjustment, the statement would have to inserted into it too, given the column names. But then it would depend on which table contains the New* columns rather than the ordering of the tables in the source and query - or the statement would fail and so neither table gets updated.

Hany Helmy SSChampion Points: 13921 More actions · Answer 14

robertjtjones (9/9/2014)
Hmmm - the SalesAdjustments table doesn't exist in the statement 😛

It`s a typo I think, but still the answer is wrong!

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant