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

No. You need to know the schema of the table to write the insert statement properly.

You need to be able to write the statement in the form of:

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

Answer 2

Absolutely no way to do this. Primary Key declaration implies this field is unique. You can't have a non unique PK. There is no way to create a row with existing PK in the same table.

Answer 3

Well, since I was unable to do this the way I wanted, I resorted to using the implicit row id, which handily enough has the same name as the rowId column I defined explicitly, so now I can use the query I had in the question, and it will insert all the data with a new rowId. To keep the rest of the program working, I just changed SELECT * FROM table to SELECT rowId,* FROM table and everything's fine.