SQL Server insert EXCEPT without selecting from a table Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of SQL Server insert EXCEPT without selecting from a table without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I want to insert some people into a user table using:

INSERT INTO [MyTable] (col2, col3)  
VALUES ('Jim', 'Bob'),
       ('Stinky', 'Pete'),
       ('Old', 'Frank')

I don’t want those entries entered if they already exist (there is an ID column, but the uniqueness here should be dictated by a composite of those two columns.

If I was selecting these values from a table I could use (I think):

INSERT INTO [MyTable] (col2, col3)
   SELECT [Name1], [Name2] 
   FROM [ExistingTable]
   EXCEPT 
   SELECT [col2], [col3] 
   FROM [MyTable]

So how can I do this EXCEPT clause when my data isn’t in a table already?

Answer

First, you should have the database validate uniqueness:

create unique index unq_mytable_id on mytable(id);

This is not needed if id is already declared as a primary key — which, presumably, it should be.

Then, you can just include a list using values():

INSERT INTO [MyTable] (col2, col3)
   SELECT v.*
   FROM (VALUES ('Jim','Bob'), ('Stinky','Pete'), ('Old','Frank')
        ) v(col2, col3)
   EXCEPT
   SELECT col2, col3
   FROM mytable;

Of course, NOT EXISTS also works, but this is closer to your original query.

We are here to answer your question about SQL Server insert EXCEPT without selecting from a table - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji