Get Primary Key of recently inserted records

• stricknyn

  SSCertifiable

  Points: 6382

  More actions

  October 7, 2008 at 12:16 pm

  #131583

  Hi all,

  Hope I can explain this well. Is there a way I can get the primary key (identity field) of the most recent inserted records?

  So for example, I have a data flow which inserts records from a source sql table into a destination sql table. After inserting, I want to grab the ID field (identity field) of all the records I just inserted and load those into another table.

  Source

  Field1

  This

  is

  a

  test

  Destination

  ID, Field1

  78, This

  79, is

  80, a

  81, test

  So 78,79,80, and 81 would get dropped into an ID column in another table.

  Can this be done?

  Thanks,

  Strick

• thasteve

  SSCrazy

  Points: 2707

  More actions

  October 8, 2008 at 2:34 am

  #882133

  if i understand your situation right you can accomplish this by using output http://msdn.microsoft.com/en-us/library/ms177564.aspx

• Prachi-885067

  SSC Veteran

  Points: 208

  More actions

  October 8, 2008 at 2:36 am

  #882134

  You can use OLEDB command for the same.

  Write a stored procedure which will insert a record into table and then select @@IDENTITY value as output parameter.

  Create a new column(for identity) with datatype numeric using derived column with default value 0.

  You can call this stored procedure and then map the output of procedure to identity column created.

  The identity value will be available for further processing.

• Jerry Hung

  SSChampion

  Points: 12908

  More actions

  October 8, 2008 at 2:45 pm

  #882590

  thasteve (10/8/2008)

  ---

  if i understand your situation right you can accomplish this by using output http://msdn.microsoft.com/en-us/library/ms177564.aspx%5B/quote%5D

  I almost forgot about this, and I just read 2 2008 books 🙁

  @@IDENTITY or SCOPE_IDENTITY() I believe only return 1 record, so may not work in this case

  SQL Server 2005 version of OUTPUT clause

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

  USE AdventureWorks;

  GO

  DECLARE @MyTableVar table( ScrapReasonID smallint,

  Name varchar(50),

  ModifiedDate datetime);

  INSERT Production.ScrapReason

  OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

  INTO @MyTableVar

  VALUES (N'Operator error', GETDATE());

  --Display the result set of the table variable.

  SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

  --Display the result set of the table.

  SELECT ScrapReasonID, Name, ModifiedDate

  FROM Production.ScrapReason;

  GO

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005