April 22, 2009 at 2:00 pm
I am working with a sales quote database. I have been charged with the task of creating a new quote record based on a previous one. I have a Header table and a few Detail tables. I am having a hard time creating copies of the sub records in that I need to assign the new PrimaryKey from the header table as a secondary key to the new records in the detail tables. I am trying to do this all via stored procedure. Any help would be appreciated.
Rob
April 22, 2009 at 2:26 pm
Check out the scope_identity() function. You can insert the new header record. then you can retrieve the last identity value to use in your new children records. Hope that makes sense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 2:33 pm
If you need more than one of the identity values at a time (inserting multiple rows in the header table), you can use the "Output" clause instead of Scope_Identity(). Details are in Books Online. It's one of the coolest of the features that were added to SQL 2005.
- 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
April 22, 2009 at 3:00 pm
This could work for me. How do I make a variable equal to SCOPE_IDENTITY()? The variable needs to be an integer. Is it as easy as:
@NewRFQNumber int
set NewRFQNumber = SCOPE_IDENTITY
April 22, 2009 at 3:07 pm
close...
declare @NewRFQNumber int
select @NewRFQNumber = SCOPE_IDENTITY()
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2009 at 3:34 pm
Works GREAT. Thank you very much!
April 22, 2009 at 5:25 pm
Again, you should know about the OUTPUT feature of the INSERT and UPDATE statements. Using this will allow you to create a procedure that could update multiple records in a single pass.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 4:21 am
Thanks. I will keep OUTPUT in mind for my next project. It does look promising.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply