SQL Server 05: Inserting to new table and getting ID from an old and the newly created ID from new

I have 3 tables, one that has already been prepopulated with data and 2 that are brand new.

My first table (the prepopulated one) looks like this:

c开发者_开发知识库ommentId, commentText, user_id, moderated, isDeleted, date_created

My second table looks like:

ActionID, moderated, isDeleted, reasonText, isReffered, isAllowed

and my third table looks like:

ID, ActionID, CommentID

What i'd like to be able to do is:

Insert into ActionTable (moderated, isDeleted)
Select moderated, isDeleted
From CommentTable

and grab the original commentID and newly created ActionID to be inserted into my third table ActionCommentLink

Cheers all.

I assume you have an auto-identity on ActionID?

Why do you need to have a ActionCommentLink? Based on the process you describe there is no n:m relation (yet).

If this is a one-time effort, the fastest way would be to temporarily add a commentId column to the ActionTable and populate it with the insert. If you don't actually need the ActionCommentLink, you are done already. Otherwise query the ActionTable to populate the Link, then remove the commentId from the ActionTable.

If you need to do this more than once, use a cursor and use the @@Identity function to query the last inserted ActionID

Just use OUTPUT. This is straight from the help file:

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO