Solved

SQL - generate a primary key in a stored procedure insert

Posted on 2011-03-22
9
460 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:eviglotti
9 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196101
Hi,

You can use ROW_NUMBER function.

check out this link.

http://msdn.microsoft.com/en-us/library/ms186734.aspx


- Bhavesh
0
 

Expert Comment

by:gkuhrd2001
ID: 35196112
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
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 35196192
Check the max(ID) and alter the identity count from the count onwards... this will not need any change in your SP.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:eviglotti
ID: 35196262
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.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196290
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
0
 

Accepted Solution

by:
eviglotti earned 0 total points
ID: 35196875
HI all,

I apologize, but it turns out that the application that I have is no longer having trouble with the identity column, so I'm no longer in need of this solution. I really apologize.

Thanks.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196889
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35198623
>>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.

0
 

Author Closing Comment

by:eviglotti
ID: 35229995
It turns out I didn't need this after all because of something else that was a configuration change in my application.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Updating statistics with error notification email in SQL server 4 109
Bubble user-defined Sql RAISERROR(...) to c# exception 14 189
Analysis of table use 7 54
Are triggers slow? 7 11
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question