tlewis-993411 (5/13/2010)
I'm not sure why the following code worksINSERT TestTable (string)
OUTPUT inserted.*
Doesn't the select return more columns (ID and String) than the insert list?
When a row is inserted into a table, SQL Server fills all columns in that row. All these columns are contaned in the 'inserted.*' construct. Some of these values are explicit, and some are implicit (NULL, identity, default value, computed value etc).
Here is an example:
create table #t
( id int identity,
a varchar(10),
b varchar(10) default 'hello',
c varchar(10),
computed_column as b + ' ' + c
)
insert #t (c)
output inserted.*
values ('test')
In the QOTD, the construct 'OUTPUT inserted.*' is equal to 'OUTPUT inserted.id, inserted.string'. 'Inserted.string' is an explicit column, while 'inserted.id' is an impilcit column.