3

OUTPUT clause vs. scope_Identity() What is difference? advantages, disadvantages?

As an example,

create table myTable 
(   
Id int identity(1,1) primary key clustered,   
myData varchar(50) not null   
);

create table #myTempTable (i int not null);

insert myTable (myData)

output inserted.Id into  #myTempTable

values ('MyData');

select * from #myTempTable
Brandan
  • 14,735
  • 3
  • 56
  • 71
Daniil Grankin
  • 3,841
  • 2
  • 29
  • 39
  • I think there aren't enough information about OUTPUT in answers – Daniil Grankin Mar 13 '13 at 13:14
  • Output is preferred method now, and it allows you to get inserted id for multiple inserts done ex: `insert .. ouput id, otherkeys into table select col1, col2 ..` Refer to [this answer](http://stackoverflow.com/a/481412/125551) from my link above – rs. Mar 13 '13 at 13:19
  • I know about it. Perhaps something else? – Daniil Grankin Mar 13 '13 at 13:24
  • @rs. I still prefer `SCOPE_IDENTITY` if only inserting a single row and I need to assign it to a scalar variable or output parameter so I don't have to mess around creating a table/table variable to hold the `OUTPUT` – Martin Smith Mar 13 '13 at 13:24

0 Answers0