Explore Videos MSDN eNews Social
Windows
Web
Phone
Cloud
Visual Studio
Security
ALM
Breakpoint
Canada Does Windows Azure
More
The latest on developer tools and technologies you care about.

Sign Up
Latest Editions
Previous Editions  
Stay connected through on your favourite social network.

Twitter
Facebook
LinkedIn

How to Move Data from One Table to Another

How to Move Data from One Table to Another

  • Comments 2

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-worked 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 SELECT clause on the INSERT 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

This post is also available on Susan Ibach’s Blog

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Your approach only works for empty target tables -- use the MERGE statement with tables of pre-existing data.

  • @mycall - actually the INSERT INTO NewNhlPlayers SELECT * FROM NhlPlayers will work even if there are existing records in the NewNhlPlayers table. You had me second guessing myself, so I just tried it in SQL Server 2008. Of course this will only work if you are not violating any primary keys.

    But you do bring up a great resource: the MERGE statement! Love MERGE! MERGE allows you to do really cool stuff, for example I could say if you don't find a matching record in the NewNhlPlayers table insert the record, but if you do find a match Update the record with the new team name. Fabulous resource for updating data warehouse tables using TSQL.

    MERGE NewNhlPlayers NP

    USING (SELECT playerid, name, team FROM NhlPlayers) as SRC

    ON (NP.playerid = SRC.playerid)

    WHEN MATCHED

    THEN UPDATE SET NP.team = SRC.team

    WHEN NOT MATCHED

    THEN INSERT (playerid, name, team)

    VALUES (SRC.playerid, SRC.name, SRC.team)

Page 1 of 1 (2 items)