Getting the sequenced id of a row to be the foreign key of a row in another table in one query

Question

The database is SQL Server 2012.

I'm supposed to add a bunch of rows on two tables from an Excel file.

I have the table Customers:

id | firstname | lastname
1  | John      | Doe
etc.

The Customers table has a sequence customers_seq to use for ids for new rows. Because at the time of insertion the amount of rows is not known. As follows:

    insert into Customers (id,firstname,lastname)
    values
    (next value for customers_seq, '2016-001', 'John', 'Doe'),
    (next value for customers_seq, '2016-002', 'Jane', 'Doe'),
    (next value for customers_seq, '2016-003', 'Steve', 'Waters'); 
-- tons of more customers --

This works as intended.

I also have the table Services

id | name | fk_Customers
1  | lunch| 2
etc.

Now, here's the problem:

I'm supposed to - in that same query where I add the Customers rows- to add a row on the table Services after each row added to the table Customers so, that the sequence-generated id for the Customers row would become the value for the column fk_Customers on the row that is added to the Services table.

I was thinking it might be possible by using the TSQL Local variables.

So, something like:

DECLARE @sequenceCreatedId bigint;  
SET @sequenceCreatedId = next value for customers_seq; 

insert into Customers (id,firstname,lastname)
values(@sequenceCreatedId, '2016-001', 'John', 'Doe')

insert into Services (id,name,fk_Customers)
values(next value for services_seq, someName, @sequenceCreatedId);

--And just repeat that whole thing. Setting that variable again and again--

SET @sequenceCreatedId = next value for customers_seq; 

insert into Customers (id,firstname,lastname)
values(@sequenceCreatedId, '2016-002', 'Jane', 'Doe')

insert into Services (id,name,fk_Customers)
values(next value for services_seq, anotherName, @sequenceCreatedId);

Is there a better way to do this?

Answer 1

Sure, use the inserted portion of the output clause to get all of them at once:

declare @customers table (
    id int not null identity(0, 1)
  , firstname nvarchar(100) not null
  , lastname nvarchar(100) not null
);

insert into @customers (firstname, lastname)
output inserted.*
values ('John', 'Doe')
     , ('Jane', 'Doe')
     , ('Steve', 'Waters');

My example doesn't use a sequence but it'll work the same way. Note that this will work for an update or delete as well; one can even get the old and new values in one shot using both deleted and inserted:

update a
   set a.FirstName = Convert(nvarchar(100), NewId())
output deleted.FirstName as OldFirstName
     , inserted.FirstName as NewFirstName
from @customers as a;