I have a table with data about a customer, Customer(name, address), with rows like "John Doe", "Some Street 123". For each row in the table, I want to insert one row in the Person(id, name) table and also one row in the Address(id, person_id, address) table.
I can accomplish this by running two insert statements for each row in Customer:
insert into Person(name) values (@name);
insert into Address(person_id, address) values (scope_identity(), @address);
But this is inefficient. I want to do the inserts in a batch, kind of like this:
-- This works, the problem is with the Address table...
insert into Person(name)
select name from Customer
-- This looks good but does not work because name is not unique.
insert into Address(person_id, address)
select p.person_id, c.address
from Customer c join Person p on c.name = p.name
there is no way to do this as you explain because you lost scope_identity() value of each row of first insert.
A work around may be add Customer primary key fields to Person table and then make join of second insert with this fields:
before insert create customerID field on Person
alter table Person add customerID int null;
then bulk inserts:
-- inserting customerID
insert into Person(name, customerID)
select name, customerID from Customer
-- joining on customerID.
insert into Address(person_id, address)
select p.person_id, c.address
from Customer c
join Person p on c.customerID = p.customerID
after that you can remove customerID field from Person table:
alter table Person drop column customerID