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]GOI 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 2Violation 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? |
|
|
cajina
Starting Member
15 Posts |
Posted - 2008-02-11 : 14:49:22
|
Can I specify the format ("0000000001" or "0000000010"), How does it work? |
|
|
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" |
|
|
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> ) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 intSelect @MaxID = Max(ID) From tblProveInsert Into tblProve (ID, tempCountrys)Select Right( '000000000' + Cast(@MaxID + Row_Number() Over (Order By myField) as varchar), 9),myFieldFrom myTable |
|
|
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 intSelect @MaxID = Max(ID) From tblProveInsert Into tblProve (ID, tempCountrys)Select Right( '000000000' + Cast(@MaxID + Row_Number() Over (Order By myField) as varchar), 9),myFieldFrom myTable
NOT recommended...Use one of the previous suggestions involving Identity columns.e4 d5 xd5 Nf6 |
|
|
cajina
Starting Member
15 Posts |
Posted - 2008-02-12 : 13:58:23
|
Thank you..! |
|
|
|