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
 Automatic ID in a Primary Key?

Author  Topic 

cajina
Starting Member

15 Posts

Posted - 2008-02-11 : 14:33:09
CREATE TABLE [dbo].[tblProve](
[ID][varchar](50)NOT NULL,
[tempCountrys][varchar](100) NULL,

CONSTRAINT [PK_tblProve] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I have a table "dbo.tblProve" that have a Primery Key ID of type
varchar(10)
and this primary key have a Default value or Binding like this:
([dbo].[fnc_ProveNewID]())

This function "dbo.fnc_ProveNewID()" finds the max(ID) in the same table and creates the next ID.

When I insert a record into the table dbo.tblProve the function creates the new ID automatically (this is the format of the ID '0000000001' or '000000010' etc. ). But when I insert a lot of records from a Select Query it displays this message below:

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_tblProve'. Cannot insert duplicate key in object 'dbo.tblProve'.

The statement has been terminated.

Is there any way to create a new ID into a table automatically without to be inserted by the user or client?.


jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-11 : 14:36:15
Why not use an IDENTITY column?
Go to Top of Page

cajina
Starting Member

15 Posts

Posted - 2008-02-11 : 14:49:22
Can I specify the format ("0000000001" or "0000000010"), How does it work?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 14:53:45
IDENTITY columns are INTEGERs.
You can add a calculated column to display the value formatted the way you want to.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-11 : 14:55:35
It will only return an integer so you cannot specify the format with the leading zeros.

CREATE TABLE IdentityTest ( i INT IDENTITY(1, 1) PRIMARY KEY, <other columns> )
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 14:56:05
It took me a while to find the article, but here it is
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-11 : 22:49:32
I'm assuming you are using SQL Server 2005. This code should do it for you.

Declare @MaxID int
Select @MaxID = Max(ID) From tblProve

Insert Into tblProve (ID, tempCountrys)
Select
Right(
'000000000' +
Cast(@MaxID +
Row_Number() Over (Order By myField) as varchar),
9),
myField
From myTable
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-11 : 23:20:52
quote:
Originally posted by Qualis

I'm assuming you are using SQL Server 2005. This code should do it for you.

Declare @MaxID int
Select @MaxID = Max(ID) From tblProve

Insert Into tblProve (ID, tempCountrys)
Select
Right(
'000000000' +
Cast(@MaxID +
Row_Number() Over (Order By myField) as varchar),
9),
myField
From myTable


NOT recommended...
Use one of the previous suggestions involving Identity columns.

e4 d5 xd5 Nf6
Go to Top of Page

cajina
Starting Member

15 Posts

Posted - 2008-02-12 : 13:58:23
Thank you..!
Go to Top of Page
   

- Advertisement -