Copy table to a different database on a different SQL Server

Easily information from t table to another table can be copied. If these tables are from two different database on the same server it also be possible to perform query using the following:



INSERT INTO bar.tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo.tblFoobar



or

SELECT *
INTO DestinationDB.MyDestinationTable
FROM SourceDB.MySourceTable



The problem arise when we want to copy information from he table of one database to that of another database. And in sqo server this can be performed by a technique known as linked server.
------------------------


Yes. add a linked server entry, and use select into using the four part db object naming convention.

select * into targetTable from [sourceserver].[sourcedatabase].[dbo].[sourceTable]




................
We can add a linked server entry following the below information


1. Click Start, click All Programs, click Microsoft SQL Server 2005 and then click SQL Server Management Studio.


2.In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.


3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.


4. In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.

5.This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server.



Under Server type, click SQL Server.

6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.


7.You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.


8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

To use impersonation, your SQL Server configuration and login accounts must meet the requirements for delegation. For more information about impersonation and delegation, see http://go.microsoft.com/fwlink/?LinkID=132854.



9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the Rpc and Rpc Out parameters to True, and then click OK.