Bulk Upload in c# and Sql server

Question

0.00/5 (No votes)

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.

Regards,
Sabbi

Posted 11-Nov-12 23:12pm

sabbi26

Add a Solution

Comments

Red Chocolate 12-Nov-12 5:22am

check whether your maximum value changes

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

shahzads · Answer 1 · 2012-11-11T23:51:00

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%'