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.
- Ensure that in SQL Mangler (I mean SSMS) you are connected to both servers with a user that has access to both databases.
- Open a query window for your target database
- Run the following command replacing server.domain.com with the FQDN of the source server
exec sp_addlinkedserver [server.domain.com];
- Run your INSERT INTO statement as follows:
INSERT INTO targetTableName ([Col1], [Col2], [Col3], [etc])
SELECT [Col1], [Col2], [Col3], [etc]
- 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!
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.
I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.
I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.
I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).