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
Posted
Updated 14-Oct-14 13:37pm
v2
Comments
[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...
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!
 
Share this answer
 
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
 
Share this answer
 
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...
SQL
INSERT INTO issuemaster
    SELECT
        serialid
    FROM
        serialmaster S
    WHERE
        NOT EXISTS (
            SELECT
                serialId
            FROM
                issuemaster I
            WHERE
                S.serialid = I.serialid)
 
Share this answer
 
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.
 
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:

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

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