How to prevent insert duplicate id in sql server

Question

3.40/5 (2 votes)

See more:

SQL-Server-2008R2

, +

I have two table first one is serialmaster and second one is issuemaster. When i added item in serialmaster then serialid is auto-generated and serialid is number added in issuemaster.

Let's i added 5 items so it can generated serialid as

serialid  itemname
1           Test1
2           Test2
3           Test3
4           Test4
5           Test5

issuemaster table as below

issueid      serialid
1              1
2              2
3              3
4              4
5              5

But when i added again 2 items in serialmaster then

serialid  itemname
1           Test1
2           Test2
3           Test3
4           Test4
5           Test5
6           Test6
7           Test7

But issuemaster table is like below.

issueid  serialid
1          1
2          2
3          3
4          4
5          5
6          1
7          2
8          3
9          4
10         5
11         6
12         7

So issuemaster have duplicate entry found. SO how can prevent it ?
I can work in store procedure of SQL Server 2008 R2

Posted 2-Oct-14 19:22pm

kalsa

Updated 14-Oct-14 13:37pm

Nelek

v2

Add a Solution

Comments

[no name] 3-Oct-14 1:47am

Hi dude could you please post your Stored procedure

5 solutions

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)

Kornfeld Eliyahu Peter · Answer 1 · 2014-10-14T11:00:00

The best way to prevent duplicate values in SQL table is to create unique keys using the proper column...
http://www.w3schools.com/sql/sql_unique.asp[^]
This is the way SQL should work and not by writing any code in stored procedure...Just remember that stored procedure can be bypassed where unique constrains can not - ever!

aarif moh shaikh · Answer 2 · 2014-10-02T19:54:00

Solution 2

Create this Stored procedure ... You will get your Solution

SQL

create procedure sp_by_aarif
@item_id int=0,
@item_name varchar(50)= ''
AS
BEGIN
   if exists (select * from yourTableName where item_id=@item_id)
       begin
            Insert into yourTableName values (@item_id,@item_name)
       end

end

Note: Here give your Table name rather than "yourTableName"

Thanks
AARIF SHAIKH

Posted 2-Oct-14 19:54pm

aarif moh shaikh

Updated 2-Oct-14 21:06pm

v2

Comments

Divyam Sharma 3-Oct-14 3:07am

Nice Sir

Kornfeld Eliyahu Peter 14-Oct-14 17:02pm

So everywhere I think to insert into that table I have to ask about existing item_id?
I wonder why SQL invented unique constrains...

Arora_Ankit · Answer 3 · 2014-10-14T05:56:00

Solution 3

SQL

INSERT INTO issuemaster
    SELECT
        serialid
    FROM
        serialmaster S
    WHERE
        NOT EXISTS (
            SELECT
                serialId
            FROM
                issuemaster I
            WHERE
                S.serialid = I.serialid)

Posted 14-Oct-14 5:56am

Arora_Ankit

v2

Comments

Kornfeld Eliyahu Peter 14-Oct-14 17:04pm

So everywhere I think to insert into that table I have add that - rather costly - existence check?
I wonder why SQL invented unique constrains...

Nelek 14-Oct-14 19:38pm

Probably to avoid having fun with performance killers ;P

Arora_Ankit 14-Oct-14 20:33pm

He didn't mentioned how he is inserting data in other table.
The only thing i got from the question was that he is using an insert statement like this

If he will add unique constraint then insert will fail by throwing error but that will not solve is problem because he need to handle those details.

Moreover this query should be executed only once after all insert in first table are done.

AndrewCharlz · Answer 4 · 2014-10-02T19:41:00

Solution 1

http://streetrat.in/Streetrat-DiscussionBox.aspx?Discussion=Using-Unique-Constraint-43&customauth=43[^]

Use Unique constraint to fix this

Posted 2-Oct-14 19:41pm

AndrewCharlz

King Fisher · Answer 5 · 2014-10-14T23:39:00

why don't you use Trigger for insert in issue master table so that there is no way of Duplicate values

refer Triggers -- SQL Server[^]

Example work:

SQL

create table id1(id int identity(1,1) primary key,name nvarchar(max))

create table id2(id int identity(1,1) primary key,serialId int)

create trigger triForId2 on id1
after insert
as
insert into id2 (serialId)select id from inserted

insert into id1 (name) values('Peter')

insert into id1 (name) values('Jack')
insert into id1 (name) values('David')
insert into id1 (name) values('Vikram')

select *From id1

select *from id2

How to prevent insert duplicate id in sql server

5 solutions

Solution 4

Solution 2

Solution 3

Solution 1

Solution 6

Add your solution here

Preview 0