SQL 如何避免在表类型sql server中插入重复记录

how to avoid inserting duplicate records in table type sql server

提问by SH.Developer

i have this query for insert list of data in table and now i want avoid insert duplicate records how i can do it in my code?

我有这个查询，用于在表中插入数据列表，现在我想避免插入重复记录如何在我的代码中做到这一点？

please help me

请帮我

-- Create a table type to match your input parameters
CREATE TYPE IdNameTable AS TABLE 
( ID INT, Name NVARCHAR(50) );
GO

-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
    @Values IdNameTable READONLY
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Group (Id, Name) 
          -- get the values from the table type parameter
          SELECT 
             Id, Name
          FROM
             @Values

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END
GO

回答by Robert

Try this way:

试试这个方法：

INSERT INTO dbo.Group (Id, Name) 
SELECT 
  Id, Name
FROM
  @Values
WHERE 
  ID NOT IN (select ID from Group)

with full code:

完整代码：

-- Create a table type to match your input parameters
CREATE TYPE IdNameTable AS TABLE 
( ID INT, Name NVARCHAR(50) );
GO

-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
    @Values IdNameTable READONLY
AS
BEGIN
    BEGIN TRY

        INSERT INTO dbo.Group (Id, Name) 
        SELECT 
           Id, Name
        FROM
           @Values
        WHERE 
           ID NOT IN (select ID from Group)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END
GO

回答by Gouri Shankar Aechoor

I have used a NOT EXISTS to check to see if ID is available in dbo.Group table and insert only when no match is found. The assumption is that ID is Unique. Please modify the filter inside the Not EXISTS to tiler for your need. Hope it helps

我使用了 NOT EXISTS 来检查 ID 在 dbo.Group 表中是否可用，并且仅在找不到匹配项时才插入。假设 ID 是唯一的。请根据您的需要将 Not EXISTS 内的过滤器修改为 tiler。希望能帮助到你

CREATE TYPE IdNameTable AS TABLE 
(ID INT, NAME NVARCHAR(50));
GO

-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
    @Values IdNameTable READONLY
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Group (Id,NAME)
        -- get the values from the table type parameter
        SELECT   v.Id,
                 v.NAME
        FROM     @Values v
        WHERE NOT EXISTS (SELECT 1 FROM dbo.Group gp WHERE gp.id=v.id)

        SELECT   0
    END TRY
    BEGIN CATCH
        SELECT   -1
    END CATCH
END
GO

回答by Taha

Easiest way is to add Uniqueconstraint to the filed while creating the table for example create table tble_Name ( ID int identity (1,1), Name nvarchar (50) UniqueNull ) When you enter a record the query shall prevent duplicate records

最简单的方法是在创建表时向字段添加唯一约束，例如 create table tble_Name ( ID int identity (1,1), Name nvarchar (50) UniqueNull ) 当您输入记录时，查询应防止重复记录

回答by SOMNATH MALI

CREATE TYPE IdNameTable AS TABLE 
(ID INT, NAME NVARCHAR(50));
GO

-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
    @Values IdNameTable READONLY
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Group (Id,NAME)
        -- get the values from the table type parameter
        SELECT   v.Id,
                 v.NAME
        FROM     @Values v
        WHERE NOT EXISTS (SELECT 1 FROM dbo.Group gp WHERE gp.id=v.id)

        SELECT   0
    END TRY
    BEGIN CATCH
        SELECT   -1
    END CATCH
END
GO