Blog » Insert From Table on Remote SQL Server

Insert From Table on Remote SQL Server

Ever have the need to populate a table from data that you have in a table on a different SQL server? This is a very handy thing to do when you are trying to add data to your dev or staging SQL databases from the live server.
Previously I would have exported the data from the one server and then imported that data into the new one.
Well brother (and sister) I’m her to tell you there is a better way!
Through the magic of Linked Servers (SQL Server 2005+ I believe) it can be done. And through the use of Synonyms, you can even make it less cumbersome if it is something that you do regularly.
How does this work you ask? Well here is how I did it.

  1. Ensure that in SQL Mangler (I mean SSMS) you are connected to both servers with a user that has access to both databases.
  2. Open a query window for your target database
  3. Run the following command replacing server.domain.com with the FQDN of the source server
    exec sp_addlinkedserver [server.domain.com];
  4. Run your INSERT INTO statement as follows:
    INSERT INTO targetTableName ([Col1], [Col2], [Col3], [etc])
    SELECT [Col1], [Col2], [Col3], [etc]
    FROM [server.domain.com].databaseName.dbo.tableName
  5. If this is something you are going to be doing a lot of, you can make a Synonym of to save you some typing:
    CREATE SYNONYM nickname FOR [server.domain.com].databaseName
    Which would give you the ability to shorten your FROM selector.

Of course, your results may vary, always test on a backup database, blah, blah blah. Enjoy coders!

Michael Gibbs
Stalk Me...
Latest posts by Michael Gibbs (see all)