I know that when I insert a row into a table with an identity column, I can retrieve the ID of the row I just inserted using SELECT @@IDENTITY.
If I do an insert statement that inserts ten rows into that same table, is there a practical way to get the IDs of all ten rows?
Yes, use the OUTPUT clause. You should be using that as a first choice anyway.
Even better you can return more than one field with OUTPUT. SO you can popuate a table variable with the surrogate key and the natural key.
The OUTPUT clause with INSERT has a lack: it is impossible to say after insert which Id was assigned for each row in source table.
declare @Persistent table (Id int not null identity primary key, Value varchar(10));
declare @Temporary table (Id int not null primary key, Value varchar(10));
insert into @Temporary values (11, 'AAA'), (22, 'AAA'), (33, 'CCC');
insert into @Persistent (Value)
output inserted.Id
select Value from @Temporary;
In the example above we will get the list of actual Ids in @Persistent table, but we can not map those Ids to Ids in @Temporary table, because OUPTUT in INSERT does not allow to get source table fields - it gets only INSERTED table fields.
As opposed to INSERT, the OUPTUT clause in MERGE allows to get fields from a source table. So using the OUTPUT with MERGE solves the problem:
merge into @Persistent as target
using @Temporary as source on source.Id = target.Id
when matched then
update set Value = source.Value
when not matched then
insert (Value) values (Value)
output
inserted.Id, source.Id;
Below is the example of using the MERGE command to insert-update-delete rows in parent-child tables. The collection of target-source-mapping from MERGE OUTPUT is very usefull:
-- definition of persistent tables
declare @Parents table (Id int not null identity primary key, Name varchar(10));
declare @Children table (Id int not null identity primary key, ParentId int, Name varchar(10));
-- imagine that persistent tables contain some data
insert into @Parents (Name) select 'Alfa';
insert into @Children (ParentId, Name) select 1, 'Delta';
-- definition of temporary tables
declare @TempParents table (Id int not null primary key, Name varchar(10));
declare @TempChildren table (Id int not null primary key, ParentId int, Name varchar(10));
-- data to insert (with negative Ids) and update (with real positive Ids)
insert into @TempParents values
(1, 'Alpha'), (-2, 'Bravo'), (-1, 'Charlie');
insert into @TempChildren values
(-9, 1, 'Alpha-1'), (-8, 1, 'Alpha-2'), (-7, 1, 'Alpha-2'),
(-6, -2, 'Bravo-1'), (-5, -2, 'Bravo-2'), (-4, -2, 'Bravo-3'),
(-3, -1, 'Charlie-1'), (-2, -1, 'Charlie-2'), (-1, -1, 'Charlie-3');
-- table to collect mapping Ids from @TempParents to @Parents
declare @ParentIdMaps table (ParentId int, TempParentId int)
-- merge data into @Parents table and collection of mapping Ids
merge into @Parents as target
using @TempParents as source on source.Id = target.Id
when matched then
update set Name = source.Name
when not matched then
insert (Name) values (Name)
output
inserted.Id, source.Id
into @ParentIdMaps
(ParentId, TempParentId);
-- merge data into @Children table and use of mapping Ids
merge into @Children as target
using
(
select
Id,
ParentId = m.ParentId,
Name
from
@TempChildren tc
inner join @ParentIdMaps m on m.TempParentId = tc.ParentId
)
as source on source.Id = target.Id
when matched then
update set ParentId = source.ParentId, Name = source.Name
when not matched then
insert (ParentId, Name) values (ParentId, Name)
when not matched by source and target.ParentId in (select Id from @TempParents) then
delete;
-- checking the result
-- see that 'Alfa' was renamed to 'Alpha'
-- and 'Delta' was deleted because it was not mentioned in @TempChildren
select
p.*,
c.*
from
@Parents p
inner join @Children c on c.ParentId = p.Id
order by
p.Id,
c.Id;
