Get last IDENTITY

• Attopeu

  Ten Centuries

  Points: 1308

  More actions

  September 30, 2010 at 7:50 am

  #228919

  Hi Guru,

  I need new solution not to have temp table to store MAX identity. Here is current business logic: we have a job execute a procedure and if there are new customers came through, the code logic is below:

  @identable (ID int)

  declare cursor forward only

  select c1,c2,c3 from t1

  open cursor

  fetch cursor

  WHILE @@FETCH_STATUS = 0

  BEGIN

  insert into @identable(ID)

  EXEC usp_InsertCustomerInfo @variable1,@variable3,@variable3

  set @New_BillingID= = (SELECT TOP 1 ID FROM @IdentTable)

  DELETE FROM @IdentTable

  The value that inserts into @Identable is from SCOPE_IDENTITY inside usp_InsertCustomerInfo procedure. Once we get max IDENTITY after inserting into customerInfo then we use that @New_BillingID value to insert into billing table like code below:

  insert into CustomerBilling(c1,c2,c3)

  select @New_BillingID,1,1,getdate()

  Are there any better options to eliminate @identable and makes code more efficient?

  Thanks very much,

  Attopeu

  Are there any other op

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  September 30, 2010 at 9:11 am

  #1229694

  Hi

  Not clear what you are trying to ask but to find the current identity of the table use

  declare @tablname varchar(100)='CustomerInfo'

  Select IDENT_CURRENT(@tablname)

  if your need to added assign a variable

  declare @tablname varchar(100)='CustomerInfo'

  declare @Newvalue bigint

  Select @Newvalue=IDENT_CURRENT(@tablname)

  /*This will give u current Max of the table */

  Select @Newvalue+1

  Thanks

  Parthi

  Thanks
  Parthi

• WayneS

  SSC Guru

  Points: 95405

  More actions

  September 30, 2010 at 10:31 am

  #1229726

  Attopeu (9/30/2010)

  ---

  Hi Guru,

  I need new solution not to have temp table to store MAX identity. Here is current business logic: we have a job execute a procedure and if there are new customers came through, the code logic is below:

  @identable (ID int)

  declare cursor forward only

  select c1,c2,c3 from t1

  open cursor

  fetch cursor

  WHILE @@FETCH_STATUS = 0

  BEGIN

  insert into @identable(ID)

  EXEC usp_InsertCustomerInfo @variable1,@variable3,@variable3

  set @New_BillingID= = (SELECT TOP 1 ID FROM @IdentTable)

  DELETE FROM @IdentTable

  The value that inserts into @Identable is from SCOPE_IDENTITY inside usp_InsertCustomerInfo procedure. Once we get max IDENTITY after inserting into customerInfo then we use that @New_BillingID value to insert into billing table like code below:

  insert into CustomerBilling(c1,c2,c3)

  select @New_BillingID,1,1,getdate()

  Are there any better options to eliminate @identable and makes code more efficient?

  Thanks very much,

  Attopeu

  Are there any other op

  If your usp_InsertCustomerInfo is doing a simple insert, then we can do all of this in some very simple, very efficient set-based ways. Meanwhile, look into the OUTPUT clause of the insert statement for getting the identity value of the record(s) that were inserted.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2