SQL Server create a row in Table B for every row in Table A and put the ID in Table A

Question

I know how to create a row in Table B for every row in Table A:

INSERT INTO [dbo].[TableB] SELECT 0, 5, 3, [TableAColumn] FROM [dbo].[TableA]

But I need to also put the ID of each new TableB row into a column in TableA, and I can't figure out how to do that.

[Edit] I forgot to mention that there are 3 TableB IDs referenced to TableA, so I can't simply make the TableB ID the same as TableA. I've thought of finding the maximum TableA ID and offsetting the second and third TableB IDs by the max and two times the max. The problem with that is I don't know how to store that maximum value between queries.

[Edit2] I think I've got the answer, TableB will be completely empty at the start of this, so if I sort TableA in the INSERT SELECT statement above, the records that are inserted into TableB should be in the same order. Then I can insert the new IDs into TableA, keeping everything sorted by ID.

Answer 1

You could always assign the TableB ids yourself, and insert them into TableB as well. In other words, don't let MySQL choose the ids for you.