# A little SQL help. How do I deal with auto\_increment when inserting from one table to another?
**URL:** https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630
**Category:** Factual Questions
**Created:** [September 11, 2009, 12:59am UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630 "2009-09-11T00:59:02Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [September 11, 2009, 12:59am UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630/1 "2009-09-11T00:59:02Z")
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?
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [September 11, 2009, 1:04am UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630/2 "2009-09-11T01:04:46Z")
How about a subquery? Something like:
INSERT INTO imagestable (filename) SELECT filename FROM pending;
ought to do the trick.
More on [INSERT SELECT](http://dev.mysql.com/doc/refman/5.1/en/insert-select.html) in the docs.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [September 11, 2009, 1:08am UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630/3 "2009-09-11T01:08:59Z")
> [@friedo](#):
>
> How about a subquery? Something like:
>
> INSERT INTO imagestable (filename) SELECT filename FROM pending;
>
> ought to do the trick.
>
> More on [INSERT SELECT](http://dev.mysql.com/doc/refman/5.1/en/insert-select.html) in the docs.
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)
---
### Author:  [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)
#### Post date: [September 11, 2009, 4:18pm UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630/4 "2009-09-11T16:18:23Z")
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
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [September 11, 2009, 6:11pm UTC](https://boards.straightdope.com/t/a-little-sql-help-how-do-i-deal-with-auto_increment-when-inserting-from-one-table-to-another/509630/5 "2009-09-11T18:11:24Z")
> [@zev\_steinhardt](#):
>
> 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
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 🙂