Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
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.
 
Regards,
Sabbi
Posted 11-Nov-12 23:12pm
sabbi261.8K
Comments
Member 9562996 at 12-Nov-12 5:22am
   
check whether your maximum value changes

1 solution

Rate this: bad
good
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:
A1
A2
A3
A4
 
You can also have
B1,
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%'
  Permalink  
v3
Comments
sabbi26 at 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 at 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
0 Sergey Alexandrovich Kryukov 598
1 OriginalGriff 280
2 George Jonsson 258
3 CPallini 190
4 Animesh Datta 130
0 OriginalGriff 5,985
1 Sergey Alexandrovich Kryukov 5,341
2 CPallini 4,760
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2014
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