SQLite help

Databases
1

Hi all, an SQLite question please.

I have a need to occasionally duplicate a record in a database table. Up till now I’ve just copied field for field and made a new record but as there are quite a lot of fields I thought there must be a better way. So, whilst searching for something like a “Duplicate” or similar command I came across something in another post that pointed me towards INSERT. So after a bit more research I came up with:

INSERT into TABLE select * from TABLE where ID = xx

Now, it would appear that “should” work as the only error I get, is, I think referring to the first field which I call ID and is the Primary Key, which obviously can’t be duplicated. If I leave that out, I am told the number of fields two not match.

At first I thought it might be because I was using the same table both times so I created a temporary holding table and changed it to:

INSERT into TMP_TABLE select * from TABLE where ID = xx

Same result. Is there a way around this, or am I stuck with a field for field copy?

Thanks for any help,
Barry

2

Replace * with each field name excluding the primary key.

3

Hi,
Thanks for the reply. 2 problems with that. I am trying to avoid it as there are around 20 fields so I’m looking for a more concise way. But also, if I do that, it still errors telling me there is a mismatch in the number of fields. (That’s my words, not the actual error message).

Barry

4

Try this:

CREATE TEMPORARY TABLE tmp_table2  AS SELECT * FROM where ID = xx

UPDATE tmp_table2 SET id = NULL      
// ID is not unique or NOT NULL in the temp table

INSERT INTO TABLE  SELECT * FROM tmp_table2
// NULL should be replaced by a valid new ID

DROP TABLE tmp_table2
1 Like
5

I use essentially this technique too, except that I ATTACH an in-memory database to the main database, and create a table in that as @Jeff_Tullin shows. Whether that’s faster or not I don’t know. In my case I’m moving an entire row from one database to another, and being able to do SELECT * is an advantage, as it’s then one less place to have a list of columns that might change in a later version of my app.

6

Hi Jeff,
Apologies for delay, I didn’t ignore you, I’m on the other side of the planet and only just saw :slight_smile:

Thanks for this, I’ll give it a try.

Barry