Sometimes I need to have a quick way of transferring data from one database to another running on a separate machine, especially when the table structures are identical or similar, it saves time. M$ SQL Server 2005 has a feature called linked servers.

New Linked Server

  1. Give a name to your server: SERVER123
  2. Choose a provider depending on the type of server you want to connect; the safest and the easiest may be ODBC so that you can link many different types of servers.
  3. Data source can be something like this:
    Driver={SQL Server};Server=myServ;Database=myDB;Uid=myUsr;Pwd=myPass;
  4. Check the security details and other options as well.

If you want to use an ODBC connection, define it first using Data Sources inside control panel of Windows.

Then, you can run a script using SQL Management Studio or your favourite database management tool such as this:

INSERT INTO TableA (column1, column2, ...)
SELECT columnA, columnB, ...
FROM SERVER123.SOMEDB.dbo.TableB

(dbo is for the schema name)

Tags: , ,
Categories: