#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    455
    Rep Power
    13

    Turn off autoincrement (temporarily) when insert...select?


    I have a massive table with an autoincrement column and I have a few hundred rows that used to be in that table but were inadvertently deleted. I have backup and have recreated the table the way it was before the deletion so I have a source from which to fetch the deleted rows. But in the meantime additional rows have been added to the real table so I can't just swap the tables.

    Is there anything wrong with my altering the real table to make the column no longer autoincrement, then doing an insert select statement to pull in the deleted rows from the backup, and then doing another alter to turn autoincrement back on? Any tricks I need to be aware of in doing this? I assume for example that I need to make a note of the autoincrement value and then when I turn autoincrement back on I need to make sure to set that value back to where it was, correct? (I use navicat so that will make life easy on checking and resetting the autonicrement value.)

    Here's the query I plan to use after I've turned off autoincrement:

    Code:
    INSERT INTO phpbb_privmsg
    SELECT pb.*
    FROM privmsg_backup pb
    WHERE pb.msg_id IN ([comma separated list of rows I want recreated]);
    Alan
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    you don't have to turn auto_incrementing off in order to insert rows with an auto_increment value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    225
    Rep Power
    11
    INSERT statement allows to say which columns have to be used. Do not include autoincrement column in clause. For example -

    Code:
    INSERT INTO table1 (column1) 
    SELECT column1 FROM table2;
    Last edited by r937; January 20th, 2010 at 08:19 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    455
    Rep Power
    13
    Right, I've got that. But I am trying to ensure that when the rows get inserted, they get inserted with the OLD msg_id (so I think I need to defeat/turn off autoincrement when I do the insertions, correct?).
    Alan
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by asinsh
    ...so I think I need to defeat/turn off autoincrement when I do the insertions, correct?
    no, not correct

    set up a test table and test it for yourself

    Comments on this post

    • asinsh agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    455
    Rep Power
    13
    Originally Posted by r937
    no, not correct

    set up a test table and test it for yourself
    As ususal, you are right (naturally!). Thanks. I did not expect insert...select to 'overpower autoincrement in that manner.
    Alan
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Sorry i did not get the solution...


    Originally Posted by asinsh
    As ususal, you are right (naturally!). Thanks. I did not expect insert...select to 'overpower autoincrement in that manner.
    I am planning to write to different tables records that contain values in other records (the new tables are on a different system but have the same structure). These tables have auto increment and if i do a streight write i will loose the connection between the records.
    I am planning to disable the auto increment on the tables, do the insert, then enable the auto increment back and set it to the higher value of (old values + 1 or highest value inserted + 1)..
    Does anyone see a problem with this?
    Or someone can elaborate on the solution that did not require disabling auto increment.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by akhashan
    Does anyone see a problem with this?
    i do
    Originally Posted by akhashan
    Or someone can elaborate on the solution that did not require disabling auto increment.
    it's simple... don't bother disabling the auto_increment

    i would give further details, but you have to go first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    i do
    it's simple... don't bother disabling the auto_increment

    i would give further details, but you have to go first
    You mean if i include the value in the insert, it will override the auto increment value?
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Yup...If i include the value in the insert, it will override the auto increment value. I guess i needed to see it and do it to believe it.

    Thank you.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by akhashan
    I guess i needed to see it and do it to believe it.
    it's pretty simple to test something like this, isn't it


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    it's pretty simple to test something like this, isn't it


    Yes...i already have the code for the prepared statement automated...just removed the default and put the old value.
    Thanks again.

IMN logo majestic logo threadwatch logo seochat tools logo