You may find this a little more intuitive. It only requires one function call to array_splice: $original = array( ‘a’, ‘b’, ‘c’, ‘d’, ‘e’ ); $inserted = array( ‘x’ ); // not necessarily an array, see manual quote array_splice( $original, 3, 0, $inserted ); // splice in at position 3 // $original is now a … Read more
( tl;dr : goto option 3: INSERT with RETURNING ) Recall that in postgresql there is no “id” concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type). If you are interested in getting the id of a newly inserted row, there … Read more
From the MySQL manual 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);
Your syntax almost works in SQL Server 2008 (but not in SQL Server 20051): CREATE TABLE MyTable (id int, name char(10)); INSERT INTO MyTable (id, name) VALUES (1, ‘Bob’), (2, ‘Peter’), (3, ‘Joe’); SELECT * FROM MyTable; id | name —+——— 1 | Bob 2 | Peter 3 | Joe 1 When the question was … Read more
Use this: ALTER TABLE users AUTO_INCREMENT=1001; or if you haven’t already added an id column, also add it ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (id);
If your DBMS does not impose limitations on which table you select from when you execute an insert, try: INSERT INTO x_table(instance, user, item) SELECT 919191, 123, 456 FROM dual WHERE NOT EXISTS (SELECT * FROM x_table WHERE user = 123 AND item = 456) In this, dual is a table with one row only … Read more
You should do batch inserts. Pseudocode: db.beginTransaction(); for (entry : listOfEntries) { db.insert(entry); } db.setTransactionSuccessful(); db.endTransaction(); That increased the speed of inserts in my apps extremely. Update: @Yuku provided a very interesting blog post: Android using inserthelper for faster insertions into sqlite database
Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row. That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, … Read more
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement. INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 ) For reference to this have a look at MOC Course 2778A – Writing SQL Queries in SQL Server 2008. For example: INSERT INTO MyTable … Read more
don’t forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous. When multiple threads will try to perform Insert-or-update you can easily get primary key violation. Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone. To avoid deadlocks and PK violations you can use something like this: begin … Read more