Generate Primary Key value using Identity Value

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

SQL Server 2005 Forums

Transact-SQL (2005)

Generate Primary Key value using Identity Value

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-12-06 : 23:16:41

Hi,

We had used this to generate primary Key value. But the problem is after deleting the last record it returns the same ID.

SELECT @FieldBlockSetupID = @EstateCode + 'R' + CAST((
CASE
WHEN
(
ISNULL(MAX(Id), -1) = -1
)
THEN 1
WHEN MAX(Id) >= 1
THEN MAX(Id) + 1
END) AS VARCHAR)
FROM Weighbridge.WBFieldBlockSetup

But this gives 1 when the table is empty. And after inserting a record also it returns 1.

SELECT @FieldBlockSetupID = 'E' + 'R' + CAST((
CASE
WHEN
(
ISNULL(IDENT_CURRENT('tABLE_1'), -1) = -1
)
THEN 1
WHEN IDENT_CURRENT('tABLE_1') >= 1
THEN IDENT_CURRENT('tABLE_1')
END) AS VARCHAR)

Help me to rectify the problem.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-07 : 00:01:03

Hi,

DECLARE @FieldBlockSetupID VARCHAR(20)

SELECT @FieldBlockSetupID = 'E' + 'R' + CAST((ISNULL(MAX(ID)+1, 1)) AS VARCHAR) FROM tABLE_1

SELECT @FieldBlockSetupID

use above statement for ur requirement.

all these @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT statements work after the insert statement only other wilse returns null values.
for more information read in BOL.

Thanks,
vikky.

baburk
Posting Yak Master

108 Posts

Posted - 2009-12-07 : 01:03:29

quote:
Originally posted by vikky

Hi,

DECLARE @FieldBlockSetupID VARCHAR(20)

SELECT @FieldBlockSetupID = 'E' + 'R' + CAST((ISNULL(MAX(ID)+1, 1)) AS VARCHAR) FROM tABLE_1

SELECT @FieldBlockSetupID

use above statement for ur requirement.

all these @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT statements work after the insert statement only other wilse returns null values.
for more information read in BOL.

Thanks,
vikky.

Hi your query will return the same ID after deleting the record which we inserted last. Since maxID(ID) will return the maximum of ID. The problem still persist.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-07 : 01:45:00

hi,

DECLARE @FieldBlockSetupID VARCHAR(20)
SELECT @FieldBlockSetupID = 'E' + 'R' + CAST((
CASE
WHEN
(
ISNULL(IDENT_CURRENT('tABLE_1'), -1) = -1
)
THEN 1
WHEN IDENT_CURRENT('tABLE_1') >= 1
THEN IDENT_CURRENT('tABLE_1')+1
END) AS VARCHAR)

SELECT @FieldBlockSetupID

Thanks,
vikky.

baburk
Posting Yak Master

108 Posts

Posted - 2009-12-07 : 23:50:39

quote:
Originally posted by vikky

hi,

DECLARE @FieldBlockSetupID VARCHAR(20)
SELECT @FieldBlockSetupID = 'E' + 'R' + CAST((
CASE
WHEN
(
ISNULL(IDENT_CURRENT('tABLE_1'), -1) = -1
)
THEN 1
WHEN IDENT_CURRENT('tABLE_1') >= 1
THEN IDENT_CURRENT('tABLE_1')+1
END) AS VARCHAR)

SELECT @FieldBlockSetupID

Thanks,
vikky.

Consider I am having a table ([DBO].[WBCustomer]) with IDENTITY column ID.

Truncate table [DBO].[WBCustomer]
DBCC CHECKIDENT ('[DBO].[WBCustomer]', RESEED, 1)
WHEN I give

select IDENT_CURRENT('[DBO].[WBCustomer]')

gives me 1

After I inserting a record also it gives me 1

Since I having another column PID which is primary key column in the table([DBO].[WBCustomer]). I have to insert like E1, E2

Since both the time it returns the same value(i.e. after reseed and inserting a single record) primary key violation error occurred.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources