How to Move Data from One Table to Another

  • Article

I recently saw a post on Stack Overflow asking how do you copy specific table data from one database to another in SQL Server. It struck me I should share the solution to this with others because it is such a handy trick. Often I set up test data and want to quickly copy it to another table, or a co-worker wants a copy of my data, or I want to copy some data from production to a local database.

If all you want to do is copy data from one table to another in the same database, just use the INSERT clause on the SELECT statement.

 INSERT INTO PlayerBackups

 SELECT * FROM NhlPlayer

If you do not have a second table and you want to make a quick and dirty backup of some test data, you can create a table based on the data you choose in your select statement.

 SELECT * INTO PlayerBackups

 FROM NhlPlayer

If you want to move data between tables across databases, you will have to use a fully qualified name

 INSERT INTO YourDatabase.YourSchema.PlayerBackups

 SELECT * FROM MyDatabase.MySchema.NhlPlayer

If you want to move data across servers, you will need to set up a linked server, this will require working with the DBA because there are authentication issues around linked servers (how will your account log in to the other server? what permissions will you have on the other server?) Once you have a linked server set up, then you can just use the fully qualified name to specify the server name.

 INSERT INTO YourServer.YourDatabase.YourSchema.PlayerBackups

 SELECT * FROM MyServer.MyDatabase.MySchema.NhlPlayer

I am always forgetting the syntax for these commands, so thought I would share them. Don’t forget, if you know SQL, you know SQL Azure! Try it out now

Comments

  • Anonymous
    September 06, 2011
    You are kidding me right?It would be SELECT * INTO PlayerBackups FROM NhlPlayer
  • Anonymous
    September 06, 2011
    @gbn - That's what happens to me when I write a blog post on a machine that doesn't have SQL installed, I couldn't cut and paste the SQL Statements and messed it up when I typed them in. Thanks for the catch. I have updated the syntax!
  • Anonymous
    September 11, 2011
    This blog post is for fullest newbies.. :)
  • Anonymous
    September 11, 2011
    Hi Serga,Although the TSQL in this post is not a new feature, I have found that if one person is asking the question then others usually have the same question, and I know I do occasionally forget the syntax for Creating a table from a Select statement so now I can look it up here when I need it :)
  • Anonymous
    May 12, 2013
    Just checking.The statements above will copy data from one table to another, but will not delete the data in the source table. Would this then be statements to COPY data from one table to another, rather than MOVE data from one table to another.Thanks
  • Anonymous
    May 12, 2013
    @ArunK - yes that would be correct, this command is used to copy data from one table to another, it would not delete the data from the original table