本文介绍了如何防止在sql server中插入重复的id的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表,第一个是serialmaster,第二个是issmaster。当我在serialmaster中添加项目时,serialid是自动生成的,而serialid是在issmaster中添加的数字。



我们添加了5个项目,因此它可以生成serialid为

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



issmaster表格如下


issuemaster table as below

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



但是当我再次在serialmaster中添加2项时


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



但是issmaster表如下所示。 br />


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



因此,issmaster发现了重复的条目。那怎么能预防呢?

我可以在SQL Server 2008 R2的存储过程中工作


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

推荐答案


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







注意:这里给你的表名而不是yourTableName



谢谢

AARIF SHAIKH




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

Thanks
AARIF SHAIKH


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


这篇关于如何防止在sql server中插入重复的id的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 20:03
查看更多