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?
LVL 1
mr-kennyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kswathiCommented:
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
mr-kennyAuthor Commented:

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?
sarabhaiDeveloperCommented:
It need to be understand what data should u move to new table from old. Pls provide sample data.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

mr-kennyAuthor Commented:
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

kswathiCommented:
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
mr-kennyAuthor Commented:
This will not work it will insert only the last id into the FK Col
sarabhaiDeveloperCommented:
OK.
Some work you need like when you create new Table create with old_id should be inserted into it.
when inserting data in FK table FK_id should insert as NULL value and after this update that FK_id values with new Table id.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mr-kennyAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.