CODEWITHSUNDEEP
sqlsql-server
SH.Developer
SH.Developer

Reputation: 187

how to avoid inserting duplicate records in table type sql server

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

Upvotes: 1

Views: 28093

Answers (4)

SOMNATH MALI
SOMNATH MALI

Reputation: 1

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

Upvotes: -1

Taha
Taha

Reputation: 11

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

Upvotes: 1

Robert
Robert

Reputation: 25753

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

Upvotes: 5

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

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

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

Upvotes: 1

Related Questions