Copy data from one table to another in the same database

There was an issue that cropped up in work today where we noticed there was a table that had been setup incorrectly that had no auto increment column for handling IDs. One of the junior developers found the bug and was telling me that he was going to export out all of the data from the table into an excelsheet, re-import the data into a new table and setup an auto increment column that way.

I guess some people just like making work for themselves :)

All that he had to do was setup a new table, let's call it TableB and give it the same columns as Table A but include an ID column and set it to auto increment and be the primary key. By running the SQL statement below, it was super quick to copy the data from one table to another. Once the data was over he could delete TableA and rename TableB to TableA.

--copy contents from Table A into Table B
Insert TableB (col1, col2, col3)
Select (Col1, Col2, Col3)
From TableA


Share this post


blog comments powered by Disqus

Updates

Follow me online at TwitterFacebook or Flickr. If you want to get in touch with me try emailing me.

Latest Tweets