October 31, 2012 at 9:19 am
Ok, weird topic name but I couldn't really think of a better title
SO I have the following scenario
table Orderheader contains
ID
...
Address
...
SiteID
When I'm inserting new records I need to lookup SiteID in the table Sites, based on the Address and some other fields, if found return the found SiteID.
Ok, that easy, but when no site is found the Site needs to be created, I already know ho to create the new ID (since the field ID in sites neither has an identity nor uniqueidentifier property and I'm not all owed to change the tablestructure) but I'm at a complete loss how to insert the new site
I know it should be easy and I will prolly facepalm but can someone give me a helping hand.
Resender
October 31, 2012 at 9:29 am
It's hard to be sure about what you need to do, but as I uderstood, you have a Sites table that is referenced by the OrderHeader table.
I'm thinking of 2 options: The classic and the new one.
The classic:
You need to verify the existance of the row in your Sites table before inserting the row into your OrderHeader table.
The new one (2008+):
Use a MERGE statement, you can use example D in this link. http://technet.microsoft.com/en-us/library/bb510625.aspx
October 31, 2012 at 9:32 am
Without getting deep into your question, would the MERGE statement be of any help.....?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 31, 2012 at 9:34 am
No it would not its still a 2008 r2 server
October 31, 2012 at 9:39 am
The MERGE command is available in 2008 and beyond.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 31, 2012 at 9:39 am
Luis Cazares (10/31/2012)
It's hard to be sure about what you need to do, but as I uderstood, you have a Sites table that is referenced by the OrderHeader table.I'm thinking of 2 options: The classic and the new one.
The classic:
You need to verify the existance of the row in your Sites table before inserting the row into your OrderHeader table.
The new one (2008+):
Use a MERGE statement, you can use example D in this link. http://technet.microsoft.com/en-us/library/bb510625.aspx
Sorry, LUIS, I missed you already suggesting MERGE. 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 31, 2012 at 9:41 am
Jason Selburg (10/31/2012)
Luis Cazares (10/31/2012)
It's hard to be sure about what you need to do, but as I uderstood, you have a Sites table that is referenced by the OrderHeader table.I'm thinking of 2 options: The classic and the new one.
The classic:
You need to verify the existance of the row in your Sites table before inserting the row into your OrderHeader table.
The new one (2008+):
Use a MERGE statement, you can use example D in this link. http://technet.microsoft.com/en-us/library/bb510625.aspx
Sorry, LUIS, I missed you already suggesting MERGE. 😛
Yeah, I thought 2008+ would meant beyond 2008 R2
So if I understand correctly the scenario would be
C. Using MERGE to perform UPDATE and INSERT operations on a target table by using a derived source table
October 31, 2012 at 10:46 am
Resender (10/31/2012)
Jason Selburg (10/31/2012)
Luis Cazares (10/31/2012)
It's hard to be sure about what you need to do, but as I uderstood, you have a Sites table that is referenced by the OrderHeader table.I'm thinking of 2 options: The classic and the new one.
The classic:
You need to verify the existance of the row in your Sites table before inserting the row into your OrderHeader table.
The new one (2008+):
Use a MERGE statement, you can use example D in this link. http://technet.microsoft.com/en-us/library/bb510625.aspx
Sorry, LUIS, I missed you already suggesting MERGE. 😛
Yeah, I thought 2008+ would meant beyond 2008 R2
So if I understand correctly the scenario would be
C. Using MERGE to perform UPDATE and INSERT operations on a target table by using a derived source table
That sounds about right.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply