How to insert duplicate rows in SQLite with a unique ID?

Question

This seems simple enough: I want to duplicate a row in a SQLite table:

INSERT INTO table SELECT * FROM table WHERE rowId=5;

If there were no explicit unique column declarations, the statement would work, but the table's first column is declared rowID INTEGER NOT NULL PRIMARY KEY. Is there any way to create a simple statement like the one above that works without knowing the schema of the table (aside from the first column)?

Answer 1

This can be done using * syntax without having to know the schema of the table (other than the name of the primary key). The trick is to create a temporary table using the "CREATE TABLE AS" syntax.

In this example I assume that there is an existing, populated, table called "src" with an INTEGER PRIMARY KEY called "id", as well as several other columns. To duplicate the rows of "src", use the following SQL in SQLite3:

CREATE TEMPORARY TABLE tmp AS SELECT * FROM src;
UPDATE tmp SET id = NULL;
INSERT INTO src SELECT * FROM tmp;
DROP TABLE tmp;

The above example duplicates all rows of the table "src". To only duplicate a desired row, simply add a WHERE clause to the first line. This example works because the table "tmp" has no primary key constraint, but "src" does. Inserting NULL primary keys into src causes them to be given auto-generated values.

From the sqlite documentation: http://www.sqlite.org/lang_createtable.html

A "CREATE TABLE ... AS SELECT" statement creates and populates a database table based on the results of a SELECT statement. A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind.

If you want to get really fancy, you can add a trigger that updates a third table which maps old primary keys to newly generated primary keys.