Insert multiple rows into a SQL Server database without duplicating

June 22, 2022

I have written this query to insert data into a table, but before inserting I am checking if the row exists already. I want to insert multiple rows – how can I make it work to insert multiple rows?

INSERT INTO dbo.User (UserID, UserName)
    SELECT LIST.ID, LIST.NAME
    FROM
        (SELECT 104 AS ID, 'James' AS STATUS_NAME) LIST
    WHERE
        NOT EXISTS (SELECT 1 FROM User US 
                    WHERE US.UserID = LIST.ID AND US.UserName = LIST.NAME)
GO

By multiple rows I mean the below example:

(SELECT 104 AS ID, 'Ross' AS STATUS_NAME) LIST
(SELECT 105 AS ID, 'Colin' AS STATUS_NAME) LIST
(SELECT 106 AS ID, 'Town' AS STATUS_NAME) LIST
(SELECT 107 AS ID, 'Hayley' AS STATUS_NAME) LIST

I know one way is below:

INSERT INTO dbo.User (UserID, UserName)
VALUES (), (), (), ()

How can I implement with checking if the record already exists before inserting for multiple users data entry in a SQL query ?

>Solution :

You could use a union and CTE to represent the tuples to be inserted. Then use an INSERT INTO ... SELECT to ensure that there are no duplicates.

WITH cte AS (
    SELECT 104 AS ID, 'Ross' AS STATUS_NAME UNION ALL
    SELECT 105, 'Colin' UNION ALL
    SELECT 106, 'Town'  UNION ALL
    SELECT 107, 'Hayley'
)

INSERT INTO dbo.User (UserID, UserName)
SELECT ID, STATUS_NAME
FROM cte t
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.User u
    WHERE u.UserID = t.ID AND u.UserName = t.STATUS_NAME
);