eviglotti
asked on
SQL - generate a primary key in a stored procedure insert
Hello,
I have a situation where having the primary key of my SQL Server 2005 table set as an identity is causing problems, so I am going to turn the identity off.
Is there an easy way to have my stored procedure generate the primary key for an inserted record with a line or two of code? Obviously I want it to be an incremental number, just as if it was an identity, but rather than SQL doing it, I want to be managing this creation within the stored procedure.
Thanks!
I have a situation where having the primary key of my SQL Server 2005 table set as an identity is causing problems, so I am going to turn the identity off.
Is there an easy way to have my stored procedure generate the primary key for an inserted record with a line or two of code? Obviously I want it to be an incremental number, just as if it was an identity, but rather than SQL doing it, I want to be managing this creation within the stored procedure.
Thanks!
HI,
You can use this SQL
First use
Begin Transaction
Select NewID=MAX(ID) from Test;
Then use this "NewID" into your Insert query
Insert into Test (ID,.....) values (newID,....);
Commit
If you need more clarification, do ask me.
Regards
Ankit
You can use this SQL
First use
Begin Transaction
Select NewID=MAX(ID) from Test;
Then use this "NewID" into your Insert query
Insert into Test (ID,.....) values (newID,....);
Commit
If you need more clarification, do ask me.
Regards
Ankit
Check the max(ID) and alter the identity count from the count onwards... this will not need any change in your SP.
ASKER
Let me evaluate and see. If the ROW_NUMBER function something that exists in SQL 2005 or just 2008?
And for each of these, how do we handle if multiple users try to insert at the same time, how do we ensure that no duplicate primary keys are assigned in these processes if two people call the stored procedure at the same time?
Thanks.
And for each of these, how do we handle if multiple users try to insert at the same time, how do we ensure that no duplicate primary keys are assigned in these processes if two people call the stored procedure at the same time?
Thanks.
Hi,
Row_Number is do exists in SQL SERVER 2005.
Regarding multiple user calling procedure same time, you must be having column with current time stamp.
based on that you can use row_number.
more over, you can use transaction.....also
- Bhavesh
Row_Number is do exists in SQL SERVER 2005.
Regarding multiple user calling procedure same time, you must be having column with current time stamp.
based on that you can use row_number.
more over, you can use transaction.....also
- Bhavesh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
no issues.....
You get one way to get incremental count.so its good for you....
We are happy to share our ideas.... =)
Enjoy
- Bhavesh
no issues.....
You get one way to get incremental count.so its good for you....
We are happy to share our ideas.... =)
Enjoy
- Bhavesh
>>so I am going to turn the identity off. <<
You cannot "turn the identity off". All you can do is rebuild the table with the column defined without the IDENTITY attribute.
You cannot "turn the identity off". All you can do is rebuild the table with the column defined without the IDENTITY attribute.
ASKER
It turns out I didn't need this after all because of something else that was a configuration change in my application.
You can use ROW_NUMBER function.
check out this link.
http://msdn.microsoft.com/en-us/library/ms186734.aspx
- Bhavesh