get autonumber on existing primary key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get autonumber on existing primary key

Author

Topic

bnf1
Starting Member

7 Posts

Posted - 2012-01-04 : 09:47:01

Is it posible to get autonumber on a existing primary key.

in my DB i have a table Opdracht.
the primary key = opdrachtnr

using access2010 i have a form that allows me to enter a new opdracht.
now i have to find the latest opdrachtnr and enter that opdrachtnr +1

some more info:

CREATE TABLE Opdracht
(
opdrachtnr NUMERIC(5) NOT NULL,
kenteken VARCHAR(8) NOT NULL,
dag DATE NOT NULL,
omschrijving VARCHAR(255),
CONSTRAINT pk_Opdracht PRIMARY KEY (opdrachtnr),
);

INSERT INTO Opdracht VALUES

(1, '12-JS-PP', '20061210', 'Motor maakt rammelend geluid'),
(2, '32-SKR-1', '20110505', 'grote beurt'),
(3, 'VR-MN-65', '20060606', 'APK + grote beurt'),
(4, '12-JS-PP', '20060707', 'APK'),
(5, '12-JS-PP', '20100808', 'grote beurt'),
(6, '12-JS-PP', '20100912', 'Uitlaat vervangen'),
(7, 'VR-MN-65', '20110814', 'schokdempers vervangen'),
(8, 'NT-LP-93', '20110317', 'banden uitlijnen')

I hope someone can help me..

Thnx,

Frank

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 09:57:50

SELECT MAX(opdrachtnr) FROM Opdracht

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-04 : 10:01:48

You can get the next value by querying the table:


select top 1 opdrachtnr+1 from Opdracht order by opdrachtnr desc

That would work well if there is only one user accessing this table and doing the updates (which I suspect is the case based on your description).

If there is the possibility that multiple users could be attempting to do this simultaenously, you should do getting the next number and doing the update in a transaction set to serializable.

Alternatively, you could just have the insertion code automatically find the next opdrachtnr and insert it.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 10:04:23

Margarita's for Breakfast?

IF It's an "autonumber" the column manages it's self.

IF it's not, then you need a "Bext Numbers" Table that you manage, AND Lock, until your INSERT operation is completed...I have that somewhere

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 10:06:40

Here

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bnf1
Starting Member

7 Posts

Posted - 2012-01-04 : 10:18:35

i am sorry
i was not clear

the opdrachtnr should be autonumber.
now i have to enter a ondrachtnr myself.
when entering a new opdracht i like it to get an uniqe opdrachtnr

and again

Thnx,

Frank

NeilG
Aged Yak Warrior

530 Posts

Posted - 2012-01-04 : 11:00:26

you could create a trigger to insert SELECT MAX(opdrachtnr)+1 FROM Opdracht after ever insert, not ideal but a solution

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 11:21:08

Is this MS Access or SQL Server

~~If it's SQL Server you can do an ALTER TABLE~~

Sorry...you have to do a create, insert....here's what sql server does


CREATE TABLE dbo.Tmp_myTable99
	(
	Col1 int NOT NULL IDENTITY (1, 1),
	Col2 varchar(10) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_myTable99 SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
	 EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, Col2)
		SELECT Col1, Col2 FROM dbo.myTable99 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT' 
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources