CODEWITHSUNDEEP
pythonsql-server
Daniel
Daniel

Reputation: 691

Prohibit Duplicate Rows Inserted into Table

Suppose I have a simple SQL table:

CREATE TABLE TestTable (
Model CHARACTER(10),
Brand CHARACTER(10)
)

Now, suppose I have inserted the following into the previously created table:

INSERT INTO TestTable (Model, Brand) VALUES ('Thunderbird', 'Ford')
INSERT INTO TestTable (Model, Brand) VALUES ('Bronco', 'Ford')

How can I construct an INSERT statement that will not insert data already contained in the table? The following INSERT statements should execute without resulting in duplicate rows:

INSERT INTO TestTable (Model, Brand) VALUES ('Fairlane', 'Ford')
INSERT INTO TestTable (Model, Brand) VALUES ('Mustang', 'Ford')
INSERT INTO TestTable (Model, Brand) VALUES ('Thunderbird', 'Ford')

Ultimately, I would like to script this insertion via Python's pypyodbc library. Any help is greatly appreciated.

Upvotes: 0

Views: 318

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

If you are looking for a SQL query to perform the insert, then you can use the following:

INSERT INTO TestTable (Model, Brand) 
SELECT t.model, t.Brand
FROM (VALUES ('Thunderbird', 'Ford')) AS t(model, brand)
WHERE NOT EXISTS (SELECT 1
                  FROM TestTable 
                  WHERE t.model = model AND t.brand = brand)

If a duplicate exists the INSERT is not executed because the SELECT returns no rows.

Demo here

Upvotes: 1

Related Questions