I have a table I just made to import a file from Excel.
I call this table test and it’s a duplicate of a real table I have earlier.
Now everything looks fine and I would like to move all my data from TEST into the REAL_TABLE
But how should I do this. I don’t want the auto_increment id’s to be copied (or will they automatically update when I export TEST and then import it to REAL_TABLE?)
Or is there any other solution on how to move all data?
INSERT INTO real_table ( all, columns, except, autoincrement )
SELECT all, columns, except, autoincrement FROM test
That gave me the following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'all, columns, except, autoincrement )
SELECT all, columns, except, autoincrement FROM test' at line 1
to copy the data from 1 table to another you can run the query
INSERT real_table SELECT * FROM test;
in php.
the auto increment in real_table then automatically increments on subsequent inserts
that’s pretty funny, that you actually ran that as is
you were supposed to remove that crap and substitute your own columns
I’m stupid. I forgot to change the part inside… Hmmmmm…
that’ll copy the auto_increment numbers, won’t it
see post #1… “I don’t want the auto_increment id’s to be copied”
please try to keep up 
you are again quoting bits of sentences out of context instead of the whole sentence.
what the op said was
I don’t want the auto_increment id’s to be copied ([B]or will they automatically update when I export TEST and then import it to REAL_TABLE?)
[/B]
yeah, i know, no need to get defensive
your solution would copy the auto_increment numbers as is
which is quite likely to cause errors
it depends on the rest of the code in the application. I’ve never had any problems with it.
you’ve probably never inserted rows into a table which already has some rows
perhaps you should test it and see 
yes I have inserted rows into a table which already has some rows.
the op didn’t specify if the table had previous rows in it or not.
if the table is always going to be empty (as it is in a lot of my use cases) just before the insert then what I posted is totally valid.
if the table is not empty before the insert then the auto increment should not be copied over.
another scenario is: even if the real table is empty, copying over the test table using the dreaded, evil “select star” will result in the same auto_increments being kept – and sometimes it is desirable to “renumber” them starting at 1
summary: my suggestion works correctly in all cases, your suggestion works correctly only sometimes
as I said before
and as i said before, my solution works in all cases, yours doesn’t
it works in 100% of the cases where I have used it as I described in
yes I have inserted rows into a table which already has some rows.
the op didn’t specify if the table had previous rows in it or not.
if the table is always going to be empty (as it is in a lot of my use cases) just before the insert then what I posted is totally valid.
if the table is not empty before the insert then the auto increment should not be copied over.
so what?
it ~doesn’t~ work in the case outlined in this thread
I can’t say with 100% surety if it did or didn’t because
the op didn’t specify if the table had previous rows in it or not.
…
let’s wait and see if we get a reply, then… but i’d be willing to take bets
besides, if there is a method that ~always~ works, why would you jump into a thread and offer one that might not work?
regardless of any reply, I would have no way of verifying if it was true or not and I don’t always just blindly accept what I read in environments like this.
regarding “might”: might, coulda, shoulda etc all lead to speculation.
Posted by Moi
the op didn’t specify if the table had previous rows in it or not.
if the table is always going to be empty (as it is in a lot of my use cases) just before the insert then what I posted is totally valid.
if the table is not empty before the insert then the auto increment should not be copied over.