Insert multiple rows into a SQL Server database without duplicating

Question

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 ?

What version of SQL are you using here?

Tim Biegeleisen
– Tim Biegeleisen

2022-06-22 04:12:28 +00:00
Commented Jun 22, 2022 at 4:12 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 22, 2022 at 4:12
SQL Server 2012

DevOpS_PRO
– DevOpS_PRO

2022-06-22 04:16:45 +00:00
Commented Jun 22, 2022 at 4:16 — DevOpS_PRO
– DevOpS_PRO, Commented Jun 22, 2022 at 4:16

Tim Biegeleisen · Accepted Answer · 2022-06-22 04:20:27Z

4

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

answered Jun 22, 2022 at 4:20

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2022-06-22 08:32:15Z

0

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.

answered Jun 22, 2022 at 8:32

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows into a SQL Server database without duplicating

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related