A little SQL help. How do I deal with auto_increment when inserting from one table to another?

Factual Questions
1

I have a table called ‘imagestable’ which has two fields…

ID (auto_increment, primary key)
Filename (text)

I also have a table called ‘pending’ with exactly the same fields.

How Do I insert the filenames from ‘pending’ to ‘imagestable’? I’ve tried to do this and I get errors each time I try different queries.

What is the proper way to do this type of data transfer?

2

How about a subquery? Something like:

INSERT INTO imagestable (filename) SELECT filename FROM pending;

ought to do the trick.

More on INSERT SELECT in the docs.

3

Strange. That’s the type of query I was trying that failed. Hence this GQ. But cutting and pasting your suggestion into the SQL box worked. I must’ve got my own sql wrong. (I’m usually pretty good at getting my sql right first time)

4

Is your error because you’re trying to insert a value into an identity column?

If so, what RDBMS are you using? If you’re using Microsoft SQL Server, you can set IDENTITY_INSERT to ON to run the query.

Zev Steinhardt

5

I was using MySQL. but friedo’s reply fixed the problem. I guess there must have been a slight error in my code. I think I was trying to do this…
INSERT INTO imagestable (filename) values SELECT filename FROM pending;

The bolded bit being my dumb mistake :slight_smile: