May 17, 2012 at 10:33 am
I have two orders tables, one is a staging, the other is a "Orders Sent" kind of table. The staging table is for me to massage data and fix it before I send a file on the missing orders to a vendor. Then I upload the missing orders into the SENT table. It's pretty straight forward and the code has been working for a while now. But we've run into a problem.
Someone has been changing order numbers.
Now I have to send a cancel order for the original order # and send a new order number for the updated record. I have a primary key that ties these two together. The steps are:
Read the Staging table & compare it to the Sent table
Verify the current order # doesn't exist in Staging
Check the Order Key to see if the key with a different order # previously exists in the Sent table
If step 3 is true, create a cancel record for the previously Sent table record & put it the Staging table
Create the file from the Staging Table
Insert Staging Records into Sent Table
Now, I can do all this, but I'm trying to learn how to use the new MERGE functionality. Before I start teaching myself though, I'd like a second opinion.
Would this would be an appropriate scenario in which to use MERGE? Or would I be banging my head against a brick wall if I tried to use it here?
May 17, 2012 at 11:27 am
Considering that you need to do two actions if you get a match (create new and cancel old), Merge isn't the right solution.
- 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
May 17, 2012 at 11:51 am
That's what I was wondering. Thank you, Gus. You have saved me some head-banging time.
May 17, 2012 at 12:01 pm
Brandie Tarvin (5/17/2012)
That's what I was wondering. Thank you, Gus. You have saved me some head-banging time.
You're welcome.
- 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
May 17, 2012 at 2:20 pm
If you want to mess around with it a bit, it occurs to me that you could use an Output clause on a Merge statement to end up inserting two rows. I don't think it'll easily do what you need, but it might.
You can actually wrap an Insert Select around a Merge statement inside a derived table by using an Output clause without Into.
Like this:
insert into dbo.MyTable (<column list>)
select <column list>
from (merge into dbo.MyTable using MySource on <rest of Merge statement> output inserted.*) as MyDerivedTable (<column names>);
Since you can do what you need in the Select clause there, you might be able to do what you're looking for. Didn't think of that at first, but remembered it just now.
Worth checking into?
- 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
May 17, 2012 at 7:03 pm
GSquared (5/17/2012)
Considering that you need to do two actions if you get a match (create new and cancel old), Merge isn't the right solution.
It is quite awkward that you cannot use THEN BEGIN ... END on any of the MERGE predicates.
Someone needs to have some stern words with the standards committee.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 7:05 pm
GSquared (5/17/2012)
If you want to mess around with it a bit, it occurs to me that you could use an Output clause on a Merge statement to end up inserting two rows. I don't think it'll easily do what you need, but it might.You can actually wrap an Insert Select around a Merge statement inside a derived table by using an Output clause without Into.
Like this:
insert into dbo.MyTable (<column list>)select <column list>
from (merge into dbo.MyTable using MySource on <rest of Merge statement> output inserted.*) as MyDerivedTable (<column names>);
Since you can do what you need in the Select clause there, you might be able to do what you're looking for. Didn't think of that at first, but remembered it just now.
Worth checking into?
I tried something like that on SQL 2008 R2 the other day but couldn't get it to work. Is that a 2012 feature or was I just a screw up?
This was my landmark 500th post!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 18, 2012 at 3:12 am
dwain.c (5/17/2012)
GSquared (5/17/2012)
If you want to mess around with it a bit, it occurs to me that you could use an Output clause on a Merge statement to end up inserting two rows. I don't think it'll easily do what you need, but it might.You can actually wrap an Insert Select around a Merge statement inside a derived table by using an Output clause without Into.
Like this:
insert into dbo.MyTable (<column list>)select <column list>
from (merge into dbo.MyTable using MySource on <rest of Merge statement> output inserted.*) as MyDerivedTable (<column names>);
Since you can do what you need in the Select clause there, you might be able to do what you're looking for. Didn't think of that at first, but remembered it just now.
Worth checking into?
I tried something like that on SQL 2008 R2 the other day but couldn't get it to work. Is that a 2012 feature or was I just a screw up?
This was my landmark 500th post!
Works for me in 2k8 R2. I only found out about this the other day, I've just assumed you had to output to a table variable in the past and will have to revisit some old code:
CREATE TABLE #test (col1 INT, col2 INT)
INSERT INTO #test VALUES(1,2), (2,1);
INSERT INTO #test (col1, col2)
SELECT col1, col2 FROM (
MERGE #test AS target USING #test AS source ON source.col1=target.col1
WHEN MATCHED THEN UPDATE SET col1=source.col2, col2=source.col1 OUTPUT INSERTED.*, $action
) a WHERE [$action]='UPDATE';
May 18, 2012 at 3:24 am
I must conclude that somehow I must have mucked it up then.
The only difference was that I wasn't doing an INSERT.
I'll have to give it another go sometime because it looks seriously cool.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 18, 2012 at 3:26 am
dwain.c (5/18/2012)
I must conclude that somehow I must have mucked it up then.The only difference was that I wasn't doing an INSERT.
I'll have to give it another go sometime because it looks seriously cool.
Yep, can only be used directly in an Insert, it can't be output to a select or you get the following error:
Msg 10729, Level 15, State 1, Line 7
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.
May 18, 2012 at 3:28 am
I don't recall that being the error I was getting but since it is pretty explicitly saying that I was trying to be a naughty boy, I suppose I shall have to cease and desist.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 18, 2012 at 3:41 am
dwain.c (5/18/2012)
I don't recall that being the error I was getting but since it is pretty explicitly saying that I was trying to be a naughty boy, I suppose I shall have to cease and desist.
Merge statements are like that. Unless it's perfect they'll shoot you down at the slightest provocation 😀
May 18, 2012 at 4:45 am
I will try the code with the Merge. I've already done the non-Merge method, which required several statements. Maybe Merge can help me compress that a bit.
May 18, 2012 at 8:46 am
Gazareth (5/18/2012)
dwain.c (5/18/2012)
I don't recall that being the error I was getting but since it is pretty explicitly saying that I was trying to be a naughty boy, I suppose I shall have to cease and desist.Merge statements are like that. Unless it's perfect they'll shoot you down at the slightest provocation 😀
Sometimes even then.... 🙂
MERGE is by far one of the most demanding and exact syntaxes I've worked with with SQL..... it's not really any more than any other statement, but so much is in one... that it's easy to get a little lost... and then get a cryptic MERGE error message back.
May 18, 2012 at 8:49 am
dwain.c (5/17/2012)
GSquared (5/17/2012)
If you want to mess around with it a bit, it occurs to me that you could use an Output clause on a Merge statement to end up inserting two rows. I don't think it'll easily do what you need, but it might.You can actually wrap an Insert Select around a Merge statement inside a derived table by using an Output clause without Into.
Like this:
insert into dbo.MyTable (<column list>)select <column list>
from (merge into dbo.MyTable using MySource on <rest of Merge statement> output inserted.*) as MyDerivedTable (<column names>);
Since you can do what you need in the Select clause there, you might be able to do what you're looking for. Didn't think of that at first, but remembered it just now.
Worth checking into?
I tried something like that on SQL 2008 R2 the other day but couldn't get it to work. Is that a 2012 feature or was I just a screw up?
This was my landmark 500th post!
I'm doing it in 2008 R2. There are pretty strict rules on what you can do with it, but I've got production code doing this.
- 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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply