Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-16 : 09:28:32
|
L3 writes "copy record and all it's related data cascading through tables into a new record cascading through all
tables with new id's generated for all.
i.e.
o insert a copy of book info into same table and get new id -- easy enough
o copy chapters relating to the ORIG book into chapters table and assoc with new id -- easy enough
0 problem comes in here, copy questions in the ORIG chapter into questions table and relate them to
the NEW chapter id's generated in above step and then
o further problem, copy the possible answers to questions from the ORIG chapter and relate them to the
NEW question id's generated for the NEW chapter in above step
Example:
INSERT INTO Books
SELECT BookName, BookAuthor, AuthEmail, Released
FROM
(SELECT BookName, BookAuthor, AuthEmail, Released
FROM Books WHERE BookID = 10)
SELECT @NEWBOOKID = @@IDENTITY
--This puts all Chapters into Chapter Table ref new BookID
INSERT INTO Chapters
SELECT @NEWBOOKID, ChapterName, ChapterOrder, ChapterDesc
FROM
(SELECT BookID, ChapterName, ChapterOrder, ChapterDesc
FROM Chapters WHERE BookID = 10)
This works just fine but then....
...for each of these Chapters that were inserted I need the new ChapterID that was generated for EACH
of them. I can grab @@IDENTITY but that only gives me the last id column of the last chapter inserted.
I need each of them to cascade through inserting questions, then I need to do the same thing for questions
to answers. Is there a way to grab the @@IDENTITY for each record to use in subsequent SQL statements?
I need to repeat this process for this and two more levels of tables.
thinking I could grab new/old identities for each and populate some mapping table but still problem with iteration! This is so easy with ASP/ADO....but don't want to make exponential calls back and forth to database." |
|
|
|
|
|
|
|
for each....next -- trying to avoid cursor