Insert multiple rows into a SQL Server database without duplicating-CodePudding

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 ?

CodePudding user response：

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
);

CodePudding user response：

A VALUES virtual table seems more concise.

INSERT INTO dbo.User (UserID, UserName)
SELECT ID, STATUS_NAME
FROM (VALUES
    (104, 'Ross'),
    (105, 'Colin'),
    (106, 'Town'),
    (107, 'Hayley')
) t(ID, STATUS_NAME)
WHERE NOT EXISTS (SELECT 1
    FROM dbo.User u
    WHERE u.UserID = t.ID AND u.UserName = t.STATUS_NAME
);

If you are passing the data in from a client app I suggest you use a Table Valued Parameter instead.