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?
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
INSERT INTO [MyTable] (col2, col3) SELECT v.* FROM (VALUES ('Jim','Bob'), ('Stinky','Pete'), ('Old','Frank') ) v(col2, col3) EXCEPT SELECT col2, col3 FROM mytable;
NOT EXISTS also works, but this is closer to your original query.