SCOPE_IDENTITY()

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  July 5, 2012 at 7:22 am

  #263708

  Hi,

  I am using an insert statement in my stored proc with SCOPE_IDENTITY().

  insert into tableA (Col1,col2,col3)

  select col1,col2,col3 from tableB

  -- above will insert 1 or more records

  SELECT @TextId = SCOPE_IDENTITY();

  Insert into tableC

  select (1,2,@TextId)

  union All

  select (3,4,@TextId)

  union All

  select (5,6,@TextId)

  Every time @TextId has same value, last identity generated. that is true as per RDBMS.

  How can I capture all the identity by insert and may utilize in my insert so that It may have @TextId equivalent to identities generated ?

• Mark Cowne

  One Orange Chip

  Points: 26984

  More actions

  July 5, 2012 at 7:47 am

  #1509121

  Use the OUTPUT clause

  http://msdn.microsoft.com/en-us/library/ms177564(v=sql.90).aspx

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• davidandrews13

  SSCertifiable

  Points: 6855

  More actions

  July 5, 2012 at 7:58 am

  #1509129

  i've only just learnt about the OUTPUT clause but it is extremely useful.

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  July 5, 2012 at 10:45 am

  #1509232

  thank you,