Link to home
Create AccountLog in
Avatar of mr-kenny
mr-kennyFlag for Switzerland

asked on

Insert a set into one table and use the new Id as FK in second Table


I want to insert a set into Tabel A and another set into Table B.
Each generated Id from Table A should be inserted as FK in Table B.

How can I do this for the complete Set?
Avatar of kswathi
kswathi
Flag of India image

You can do it in two ways:

1) Insert data into Table A and then get the primary key Id(SCOPE_IDENTITY()),  Insert  into Table B (you can use transactions for this )
2) Insert  the data in both the tables, then update the ID in Table B
Avatar of mr-kenny

ASKER


I like to to this:

INSERT INTO newTableA (Col1, Col2, Col3)
  SELECT Col1, Col2, Col3 FROM oldTableA
 
INSERT INTO newTableB (FK_A, Col2)
  SELECT ???, Col2 FROM oldTableB

each id of the newTableA should be FK in new Table B
Can you please show me a code example?
Avatar of Sara bhai
It need to be understand what data should u move to new table from old. Pls provide sample data.
what data do you need? I try to explain what I want to do:

- I hava an old Table A and I want to copy the content (150 rows) into a new Table A (Id is auto generated)

- Then I have a Content from old Table B (150 Rows) that i copy into new Table B (Id is auto generated)

- Now I want the id for each row in new Table a insert as FK into table b

Declare @TableA_ID Int

INSERT INTO newTableA (Col1, Col2, Col3)
  SELECT Col1, Col2, Col3 FROM oldTableA

Set @TableA_ID = Scope_Identity()

if  IsNULL(@TableA_ID,0) >0

INSERT INTO newTableB (FK_A, Col2)
  SELECT @TableA_ID, Col2 FROM oldTableB
This will not work it will insert only the last id into the FK Col
ASKER CERTIFIED SOLUTION
Avatar of Sara bhai
Sara bhai
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks, Yes I think using the old Keys is the easiest solution. With the old Keys i can keep the relation and Update to the new Keys,
Thanks for the support