Click here to Skip to main content
13,446,408 members (41,209 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi DB Guys,

I am inserting a bulk data into table which had a primary key as Char + Identity Column
So in SP i wrote a script like getting max of Identity and adding Char value and then i am inserting in to DB.
But the problem it's a live project traffic is more
so my script is not working in Prod
It's throwing error Primary key violation.

I hope you guys understand my issue and u guys already faced this issue.
So please provide some solution for this.

Posted 11-Nov-12 23:12pm
Member 9562996 12-Nov-12 5:22am
check whether your maximum value changes

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You cannot add the record just based on increasing the identity. Your primary key works on combination of char and int(identity)
Say for eg:

You can also have
C1, etc.

So just by increasing the max of identity will not work as it will violate the primary key when it finds the combination of chat and int already in DB. I think you are auto-incrementing you PK using some logic not using the identity column from DB. if your PK one field with Char + Identity Column then you need the max int(identity) of char you are entering then increment that number.

so if you are entering new record with char A then first find max of like all pk starts with 'A' then increment it.

Your query will be something like this.

select 'c' + cast(( CAST( SUBSTRING(MAX(Character),2, LEN(Character)) as integer) + 1) as varchar(IDENT_CURRENT)) from table_name group by Character having Character like 'c%'
sabbi26 12-Nov-12 6:43am
Hi shahzads,

I almost used same logic

Declare @Id varchar(8) = ''
Select @Id = 'P' + RIGHT('0000000' + Cast((CAST(SUBSTRING(lastGeneratedId, 2, (LEN(lastGeneratedId) - 1)) as int) + 1) as varchar(7)), 7) from
Select MAX(ID) as lastGeneratedId
from MyTable
) as A

but it's not working.
problem is before inserting my generated ID it's inserting by another user from another session.
So i am assuming Scope_Identity or Identity_Current will solve my problem?
shahzads 12-Nov-12 8:38am
you can use IDENT_CURRENT for this as this returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope. This not dependent on scope. It is attached to table. this should work..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01-2016 | 2.8.180314.2 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100