How can I do multiple inserts without a cursor

Question

Lets say I have two tables

tblA ( tableAID INT IDENTITY(1,1), foo VARCHAR(100))

tblB ( tableBID INTIDENTITY(1,1), tableAID INT, bar varchar(100))

tblB.tableAID is a FK to tblA.

I want to insert a bunch of records (pulled from some other table in the system) into this pair of tables. I need to know what the ID from inserting into tblA is before I can insert into tblB.

Is there any way to do this without processing it row-by-row?

Answer 1

Can you use a temp table?

DECLARE @t TABLE (foo, int, ba VARCHAR(50))
INSERT INTO @t (foo, bar) SELECT foo, bar FROM your_other_table
ALTER TABLE @t ADD id_a INT IDENTITY(1, 1)

You should use SET IDENTITY_INSERT OFF to do this

Maybe you would want to reseed your table for doing a proper insert while the table is in production.

DBCC CHECKIDENT 'tblA' RESEED, newValue

Answer 2

Assuming that what you are really asking is how to avoid multiple round-trips to the server, then one way is to write a stored proc that does both inserts, and uses

Set @Pk = Scope_Identity()

after the first insert to get the Identity value created for that first insert and then uses that @Pk value for the second insert.

here's a simple example

Create Procedure SaveEmployee
@Name varChar(30),
@DivId Int,
@HomePhoneNumber VarChar(12),
@FaxNumber VarChar(12)
As
Set NoCount On
Declare @Pk Integer


   Insert Employees(Name, Divisionid)
   Values(@Name, @DivId)
   Set @Pk = Scope_Identity()
   -- ------------------------------------------------
   Insert PhoneNums(EmployeeId, PhoneType, PhoneNumber)
   Values(@Pk, 'Home', @HomePhoneNumber)
   -- ------------------------------------------------
   Insert PhoneNums(EmployeeId, PhoneType, PhoneNumber)
   Values(@Pk, 'Fax', @FaxNumber )

   Return 1

Answer 3

In SQL Server 2005 and later, you can use the OUTPUT clause to pass the new key values into a second table:

INSERT INTO T ...
OUTPUT PrimaryKeycol, otherValues INTO ChildTable;

Other possibilies exist in SQL Server 2008 with "composable DML" and the MERGE statement.

Added in response to comment:

create table T(i int identity(1,1), j int, k int default -1);
go

merge into T using (values (1), (2)) as U(j)
on U.j = T.j
when not matched then insert (j) values (j)
output inserted.i, inserted.k;
go

drop table T;

Answer 4

It's probably past the point where it's useful, but this points out an inherent difficulty with using surrogate primary keys, and especially with using them as foreign keys in other tables. If your relationships are defined using natural keys, you know from the start what values to insert.