September 30, 2010 at 7:50 am
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
September 30, 2010 at 9:11 am
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
September 30, 2010 at 10:31 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply