What's the cleanest way in SQL to break a table into two tables?

Question

I have a table that I want to break into 2 tables. I want to pull some data out of table A, put it into a new table B, and then point each record in A to the corresponding record in the new table.

It's easy enough to populate the new table with an INSERT INTO B blah blah SELECT blah blah FROM A. But the catch is, when I create the new records in B, I want to write the ID of the B record back into A.

I've thought of two ways to do this:

1. Create a cursor, loop through A a record at a time, create the record in B and post the new ID back to A.

2. Create a temporary table with the extracted data, an ID for the new record, and the ID of A. Then use this temporary table to populate B and also to post the ID back to A.

Both methods seem cumbersome with a lot of copying all the data back and forth. Is there a clean, simple way to do this or should I just knuckle down and do it the hard way?

Oh, I'm using Microsoft SQL Server, if your answer depends on non-standard features of SQL.

Someone asks for an example. Yes, I should have included something concrete to make it clear. The real example is a bunch of data, but let me give a simplified example of what I mean.

Let's say I have a Customer table with customer_id, name, and city. I want to break city out into a separate table.

So for example:

Customer
ID  Name     City
17  Al       Detroit
22  Betty    Baltimore
39  Charles  Cleveland

I want to convert this to:

Customer
ID  Name    City_ID
17  Al      1
22  Betty   2
39  Charles 3

City
ID  Name
1   Detroit
2   Baltimore
3   Cleveland

The exact ID values don't matter.

So easy enough to create the City table and the reference ...

create table city (id int identity primary key, name varchar(50))
alter table customer add city_id int references city

And then populate the city table ...

insert into city (name)
select city from customer

The trick is how to get those city IDs back into the Customer table.

(And yes, in this simplified example, the effort may appear pointless. In real life we have many tables with addresses and I want to pull all those fields out of all the other tables and put them into a single address table, so we can standardize the declarations and processing of addresses.)

(Note: I haven't tested the sample code above. Excuse me if there's a typo or something in there.)

Answer 1

You can use the output clause to capture your new ID values.

without any sample data or examples of what you are doing the following is just a guide.

Create a #table to hold the new ID values, then insert the newly inserted Id identity values along with a correlating value from the inserted virtual table. You can then update the original table with the new IDs by joining on this correlating value.

create table #NewIds (TableBId int, TableAId int)

insert into TableB (column list)
output inserted.Id, inserted.TableAId into #NewIds
select column list
from TableA

update a
set a.TableBId=Id
from #NewIds n join TableA a on a.Id=n.TableAId