How do I insert multiple values into a postgres table at once?
Multi-value insert syntax is: insert into table values (1,1), (1,2), (1,3), (2,1); But krokodilko’s answer is much slicker.
sql-insert
Avoid duplicates in INSERT INTO SELECT query in SQL Server
Using NOT EXISTS: INSERT INTO TABLE_2 (id, name) SELECT t1.id, t1.name FROM TABLE_1 t1 WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id) Using NOT IN: INSERT INTO TABLE_2 (id, name) SELECT t1.id, t1.name FROM TABLE_1 t1 WHERE t1.id NOT IN (SELECT id FROM TABLE_2) Using LEFT JOIN/IS NULL: INSERT INTO TABLE_2 (id, … Read more
“Insert if not exists” statement in SQLite
If you never want to have duplicates, you should declare this as a table constraint: CREATE TABLE bookmarks( users_id INTEGER, lessoninfo_id INTEGER, UNIQUE(users_id, lessoninfo_id) ); (A primary key over both columns would have the same effect.) It is then possible to tell the database that you want to silently ignore records that would violate such … Read more
How to speed up insertion performance in PostgreSQL
See populate a database in the PostgreSQL manual, depesz’s excellent-as-usual article on the topic, and this SO question. (Note that this answer is about bulk-loading data into an existing DB or to create a new one. If you’re interested DB restore performance with pg_restore or psql execution of pg_dump output, much of this doesn’t apply … Read more
Best way to do multi-row insert in Oracle?
In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax: INSERT ALL INTO t (col1, col2, col3) VALUES (‘val1_1’, ‘val1_2’, ‘val1_3’) INTO t (col1, col2, col3) VALUES (‘val2_1’, ‘val2_2’, ‘val2_3’) INTO t (col1, col2, col3) VALUES (‘val3_1’, ‘val3_2’, ‘val3_3’) . . . SELECT 1 … Read more
Inserting multiple rows in mysql
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example: INSERT INTO tbl_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9); Source
How to insert a value that contains an apostrophe (single quote)?
Escape the apostrophe (i.e. double-up the single quote character) in your SQL: INSERT INTO Person (First, Last) VALUES (‘Joe’, ‘O”Brien’) /\ right here The same applies to SELECT queries: SELECT First, Last FROM Person WHERE Last=”O”‘Brien’ The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string … Read more
Postgres: INSERT if does not exist already
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
How can I do ‘insert if not exists’ in MySQL?
Use INSERT IGNORE INTO table. There’s also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement. Post from bogdan.org.ua according to Google’s webcache: 18th October 2007 To start: as of the latest MySQL, syntax presented in the title is not possible. But there … Read more