Click here to Skip to main content
15,919,028 members
Please Sign up or sign in to vote.
3.40/5 (2 votes)
See more: , +
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
Updated 14-Oct-14 13:37pm
[no name] 3-Oct-14 1:47am    
Hi dude could you please post your Stored procedure

The best way to prevent duplicate values in SQL table is to create unique keys using the proper column...[^]
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!
Share this answer
Create this Stored procedure ... You will get your Solution

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


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

Share this answer
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...
INSERT INTO issuemaster
        serialmaster S
        NOT EXISTS (
                issuemaster I
                S.serialid = I.serialid)
Share this answer
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.
Share this answer
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:

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
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
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900