Do conditional INSERT with SQL?

by

You can do that with a single statement and a subquery in nearly all relational databases.

INSERT INTO targetTable(field1) 
SELECT field1
FROM myTable
WHERE NOT(field1 IN (SELECT field1 FROM targetTable))

Certain relational databases have improved syntax for the above, since what you describe is a fairly common task. SQL Server has a MERGE syntax with all kinds of options, and MySQL has optional INSERT OR IGNORE syntax.

Edit: SmallSQL’s documentation is fairly sparse as to which parts of the SQL standard it implements. It may not implement subqueries, and as such you may be unable to follow the advice above, or anywhere else, if you need to stick with SmallSQL.

More Related Contents:

  1. SQL query for multiple rows in single column [closed]
  2. Why is SELECT * considered harmful?
  3. How to check if a table exists in a given schema
  4. How do I reset a sequence in Oracle?
  5. Synchronizing client-server databases
  6. ORA-00904: invalid identifier
  7. How to create multiple one to one’s
  8. How to select unique records by SQL
  9. Fastest way to count exact number of rows in a very large table?
  10. Key value pairs in relational database
  11. T-SQL Cast versus Convert
  12. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  13. SQL – How to transpose?
  14. Splitting the string in sql server
  15. How does table alias names affect performance?
  16. Performance issue in using SELECT *? [duplicate]
  17. Need to list all triggers in SQL Server database with table name and table’s schema
  18. What is the purpose of constraint naming
  19. How can I do a BEFORE UPDATED trigger with sql server?
  20. Drop all tables command
  21. How to find a table having a specific column in postgresql
  22. Deferrable Constraints in SQL Server
  23. can we list all tables in msaccess database using sql?
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Comma separated values in a database field
  26. Can SQL Server Express LocalDB be connected to remotely?
  27. What can cause an Oracle ROWID to change?
  28. SQL: Using NULL values vs. default values
  29. Why don’t DBMS’s support ASSERTION
  30. Single or multiple databases

Leave a Comment