How to group several INSERTs?

Question

I'm inserting data from one table into several others. The first insert will create a new userid. This new userid will be used in succeeding inserts. I will also continue inserting username from the source table into other tables. The chain of inserts below are for one user. There will be probably 2000 users involved.

I'm familiar with how this can be done using a cursor. Is there some other way to do this chain of inserts without a cursor?

insert into table 1 using @username and @firstname from source table

insert into table 2 using userid generated from table 1 (userid1)

insert into table 3 using @username and userid1

insert into table 4 using userid1

You can use the output clause (http://msdn.microsoft.com/en-gb/library/ms177564(v=sql.100).aspx) to capture the ids in bulk, if you can arrange your inserts in a set like fashion. — Laurence, Jan 02 '13 at 23:27
I'm familiar with the output clause. Do you have an example of what you mean by set like fashion? — 4thSpace, Jan 02 '13 at 23:38
Ok, I see. Thanks. But how do you built it when 4+ tables are involved? — 4thSpace, Jan 02 '13 at 23:59
You'd need to give more information about what your second insert is doing, but here's a more extended example http://sqlfiddle.com/#!3/31110/3 — Laurence, Jan 03 '13 at 00:09
Nice. I'll work with but that should do for this question. I'll create a new one off of whatever issue I run into if needed. Can you post as answer? — 4thSpace, Jan 03 '13 at 00:13

score 1 · Accepted Answer · answered Jan 03 '13 at 00:16

You can use the Output Clause of an Insert statement to capture generated Ids in bulk.

For example:

Create Table dbo.Source (
  FirstName nvarchar(100),
  LastName nvarchar(100)
);

Create Table dbo.Attrs (
  Id int Identity Not Null Primary Key,
  Name nvarchar(100) Not Null,
  DefaultVal nvarchar(100)
);

Create Table dbo.Table1 (
  Id Int Identity Not Null Primary Key,
  FirstName nvarchar(100),
  LastName nvarchar(100)
);

Create Table dbo.Table2 (
  Id int Identity Not Null Primary Key,
  Table1ID int Not Null Foreign Key References dbo.Table1 (Id),
  AttrId int Not Null Foreign Key References dbo.Attrs (Id)
);

Insert Into dbo.Source Values
  (N'Mickey', N'Mouse'),
  (N'Donald', N'Duck'),
  (N'Goofy', Null);

Insert Into dbo.Attrs Values
  ('Size', 'Small'),
  ('Wings', 'No');

Declare @Temp1 Table (Id Int, FirstName nvarchar(100), LastName nvarchar(100))
Declare @Temp2 Table (Id int, Table1ID int, AttrId int)

Insert Into dbo.Table1
  (FirstName, LastName)
Output 
  inserted.Id, inserted.FirstName, inserted.LastName
Into 
  @Temp1
Select
  FirstName, LastName
From
  dbo.Source


Insert Into dbo.Table2
  (Table1ID, AttrId)
Output
  inserted.Id, Inserted.Table1ID, Inserted.AttrID
Into
  @Temp2
Select
  t.Id,
  a.Id
From
  @Temp1 t
    Cross Join
  dbo.Attrs a

Select * From @Temp2

http://sqlfiddle.com/#!3/31110/3

Thanks. A follow up question if you are interested: http://stackoverflow.com/questions/14143750/how-to-supply-values-to-sproc-from-table. — 4thSpace, Jan 03 '13 at 17:13

How to group several INSERTs?

1 Answers1