Blog

Retrieving identity values in T-SQL – Advanced

  • 21 January 2019
  • 0 replies
  • 178 views
Retrieving identity values in T-SQL – Advanced
Userlevel 7
Badge +5
A while ago, a blog was published about retrieving identity values in T-SQL using the Output-clause. This blog continues on this subject for a more advanced scenario.

The case

There are some situations where you don’t just want the identities as output, but you also want to match them to related records which were used in the insert statement. For instance, when copying multiple order records including the order_line records, you need to match the new order_id with the source order_id in order to copy the order_line records properly.

Now don’t get me wrong, there are other ways to achieve our goal of mapping the inserted order_id identity with the source order_id:

  • We could store a foreign key called source_order_id in the newly created order record. However, this would require a data model change and we do not need this column outside of this transaction.
  • Alternatively, we could try to find the source order record using matching columns. At this point, we would treat these other columns a so-called surrogate key. However, a good set of columns might be hard to find and would require an unique constraint to really be fool-proof.
  • The last option would be to create a cursor which provides a row-based approach. Every inserted order record can be mapped to the current source order record with ease. The sad part is that set-triggers on the order table will be executed once for every row. Performance-wise, this is the worst approach.
However, it would be easier if we could use the Output--clause to make this happen.

Mapping update results


For both delete and update statements, T-SQL allows you to use columns in the Output--clause which are not directly related to the affected record. An example, where customers are inactivated and the reason is stored in the output, even though the reason is not a column of the affected record:

code:
--Create 'real' tables
declare @customer table
(
customer_id int identity,
name varchar(100),
customer_type int,
active bit
)

declare @to_inactivate table
(
customer_type int,
reason varchar(100)
)

--Fill some data
insert @customer values ('customer 1', 1, 1)
insert @customer values ('customer 2', 2, 1)
insert @customer values ('customer 3', 2, 1)
insert @customer values ('customer 4', 3, 1)
insert @to_inactivate values (2, 'Redundant data')
insert @to_inactivate values (3, 'No longer in use')

--Create table variable to store update information
declare @t table
(
customer_id varchar(100),
reason varchar(100)
)

--Update the customers and output the customer id and the reason
update c
set c.active = 0
output inserted.customer_id, p.reason into @t
from @customer c
join @to_inactivate p
on p.customer_type = c.customer_type

select *
from @t


Mapping delete results

The same thing can be done with delete statements. In the following example, the reason is stored in the output even though the reason is not a column of the deleted record.

code:
--Create 'real' tables
declare @customer table
(
customer_id int identity,
name varchar(100)
)

declare @to_delete table
(
customer_id int,
reason varchar(100)
)

--Fill some data
insert @customer values ('customer 1')
insert @customer values ('customer 2')
insert @customer values ('customer 3')
insert @to_delete values (1, 'Old data')
insert @to_delete values (2, 'Moved')

--Create table variable to store update information
declare @t table
(
customer_name varchar(100),
reason varchar(100)
)

--Delete the customers and output the name and the reason
delete c
output deleted.name, n.reason into @t
from @customer c
join @to_delete n
on n.customer_id = c.customer_id

select *
from @t


Mapping insert results?


Unfortunately, the previously shown method does not work with insert statements. The following query attempts to store the newly created identities in the output, together with the reason. The reason not being part of the inserted record. However, this code will generate an error:

code:
--Create 'real' tables
declare @customer table
(
customer_id int identity,
name varchar(100)
)

declare @to_insert table
(
name varchar(100),
reason varchar(100)
)

--Fill some data
insert @to_insert values ('customer 1', 'import 2017')
insert @to_insert values ('customer 2', 'import 2017')
insert @to_insert values ('customer 3', 'additional data')

--Create table variable to store insert information
declare @t table
(
customer_id int,
reason varchar(100)
)

--Insert the customers and output the name and the reason
insert into @customer
output inserted.customer_id, n.reason into @t
select n.name
from @to_insert n

Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "n.reason" could not be bound.

Why this is not supported still eludes me.

The solution


Fortunately, there is a work-around available. The Merge-statement allows for output of all data affected by the statement, including columns not affecting the actual modified records. With a merge statement, this also works for the inserted records.

If we change our earlier query as following, we can actually map the inserted identities with the reason. without having to rely on either a surrogate key, data model changes or a row-based approach such as a cursor:

code:
--Create 'real' tables
declare @customer table
(
customer_id int identity,
name varchar(100)
)

declare @to_insert table
(
name varchar(100),
reason varchar(100)
)

--Fill some data
insert @to_insert values ('customer 1', 'import 2017')
insert @to_insert values ('customer 2', 'import 2017')
insert @to_insert values ('customer 3', 'additional data')

--Create table variable to store insert information
declare @t table
(
customer_id int,
reason varchar(100)
)

--Insert the customers and output the name and the reason
merge @customer
using
(
select *
from @to_insert
) n
on 1 = 0 -- Force not-matched
when not matched
then
insert (name) values (n.name)
output inserted.customer_id, n.reason into @t;

select *
from @t


Granted, it does not win the prize for most elegant piece of code. But it does work properly and generates nearly the same query plan as a regular insert statement.

Final considerations


Keep in mind that columns returned from Output-show the data as it is before any triggers are executed. This also counts for instead-of triggers. You might get an output even if no actual records are modified by the instead-of trigger.

Happy coding!

0 replies

Be the first to reply!

Reply