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

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 will work, but the first column of the table is declared rowID INTEGER NOT NULL PRIMARY KEY . Is there a way to create a simple statement like the one above that works without knowing the table layout (except for the first column)?

Answer 1

Well, since I couldn’t do it the way I wanted, I resorted to using an implicit row identifier, which conveniently has the same name as the rowId column, which I defined explicitly, so now I can use the query that I have was in question and it will insert all the data with the new rowId. For the rest of the program to work, I just changed SELECT * FROM table to SELECT rowId,* FROM table and everything is fine.

Answer 2

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

In this example, I assume that there is an existing, populated table called "src", with the key INTEGER PRIMARY KEY called "id", as well as several other columns. To duplicate the src lines, 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; 

In the above example, all rows of the src table are duplicated. To only duplicate the desired line, simply add the WHERE clause to the first line. This example works because the table "tmp" does not have a primary key constraint, but "src" does. Inserting primary NULL keys into src forces them to receive automatically generated values.

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

The 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 does not have a PRIMARY KEY and no restrictions of any type.

If you want real fantasy, you can add a trigger that updates the third table, which displays the old primary keys for the newly created primary keys.

Answer 3

Not. You need to know the table layout in order to write the insert statement correctly.

You need to write an expression in the form:

 insert into Table (column1, column2, column3) select column1, column2, column3 from OtherTable where rowId = 5 

Answer 4

There is absolutely no way to do this. In the Primary Key statement, this field is unique. You cannot have a unique PC. It is not possible to create a row with an existing PK in the same table.