0

Assuming I have a table items with columns id, code.

In my scenario, I am saving 10 items to it:

insert into items (code) 
    select code 
    from other_table 
    where condition

How do I then insert into table itemDescription (id, item_id, code) where item_id would be populated by the previously inserted item id as foreign key?

Insert into itemDescription (item_id, code) 
    select <foreignkey goes here>, info.data  
    from items 
    join info on items.id = info.item_id
    where code = "C"

I know there are things like scope identity for retrieving last inserted id, but this is for individual inserts; how does that work if I'm inserting in this way all at once? Is there a way to make this work?

Typically I do a while(1=1) loop or cursor over the items, but am told this isn't "the sql way of doing things"

Avan
  • 223
  • 3
  • 13
  • 4
    Sample data and expected results will help us help you here. I suspect you're going to want to `OUTPUT ... INTO`. – Thom A Apr 27 '23 at 09:15
  • Does `id` come from `other_table` or is `id` an identtiy field? Please update the question with these details. – Nick.Mc Apr 27 '23 at 10:23
  • you can use output inserted : https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16 – abolfazl sadeghi Apr 27 '23 at 10:37

1 Answers1

-1

One of the easiest ways I find to do this whether in SQL server or in an interface that interacts with SQL server is to specify and store the result in a variable and use it in the child table.

DECLARE PARENT_ID GUID = NEWID()

That assumes you are using GUID for your ID. Then you can just replace your values in the parent and child table with PARENT_ID or whatever you choose to call it.

andhoov
  • 1
  • 1
  • 1
    That would work for a single record where the id is stored as a uniqueidentifier (minus the missing @ in the variable name), but from the text of the question I'm pretty sure the id is a numeric identity column. – Zohar Peled Apr 27 '23 at 10:36