Help with Query - Insert multiple rows and link between tables.

I am trying to do the following:

Insertn rows into A Table calledEAItems. For each row that is inserted intoEAItemsI need to take thatItemID(PK) and insert a row intoEAPackageItems.

I'm inserting rows from a Table calledEATemplateItems.

So far I have something like this: (I have the PackageID already at the start of the query).

 INSERT INTO EAItems(Description, Recommendation, HeadingID)
SELECT Description, Recommendation, HeadingID
FROM EATemplateItemsWHERE EATemplateItems.TemplateID = @.TemplateID
INSERT INTO EAPackageItems(ItemID, PackageID) ...

I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.Any Advice / help would rock! Thanks

I think you will want to do this as a stored procedure. As you insert an individual row you can use the @.@.IDENTITY variable for the last inserted row. You could save that to a variable and insert the record to the second table. In your first query you could adjust it to select the rows into a table variable and then loop over the rows in the table variable and use that loop to take care of your individual inserts.

The T-SQL snippet below is the basic structure for what I am describing.

DECLARE @.MyTableTABLE(IDint IDENTITY,Name varchar(20))INSERT INTO @.MyTable (Name)SELECT NameFROM OtherTableDECLARE @.CurIDintDECLARE @.MaxIDintDECLARE @.RowIDintSET @.MaxID = (SELECT MAX(ID)FROM @.MyTable )SET @.CurID = 1WHILE (@.CurID <= @.MaxID)BEGIN-- use CurID to access the row in @.MyTable-- do your insert-- get the @.@.IDENTITY-- use that value for the next insert-- be sure to increment the @.CurID to the next rowSET @.CurID = @.CurID + 1END

|||

Thanks for the reply.

I'll work with that when I get to work - it seems logically straight forward. The script you put down can work in both SQL 2000 and SQL 2005 right? I hope so :D

|||Yes, there is nothing specific in there for SQL Server 2005.