How to copy table data from remote server to Local server in SQL Server 2008

Question

I am trying to copy all table data from server to my local database, like

INSERT INTO [.\SQLEXPRESS].[Mydatabase]..MYTable
   SELECT * 
   FROM [www.MYSite.com].[Mydatabase]..MYTable

www.MYSite.com having SQL LOGIN ID XYZ AND PASSWORD 1234

but I get an error:

Could not find server 'www.MYSite.com' in sys.servers.
Verify that the correct server name was specified. If necessary,
execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I want to copy all the data from Mytable of www.MYSite.com to Mytable of .\SQLExpress.

How to resolve it? Please help.

Update :

I am using Microsoft Sync Framework 2.0 to sync all data from www.MYSite.com to .\SQLExpress and vice versa, but in a one condition I want to copy data from www.MYSite.com to .\SQLExpress without sync framework

Please Note I am passing those SQL Statement using C#..

Answer 1

When you specify a database on another server, like this:

SELECT * 
FROM [www.MYSite.com].[Mydatabase]..MYTable

... the server name needs to be one that the database server was previously configured to recognize. It needs to be in the system table sys.servers.

So, you need to configure your SQLExpress instance to "know about" that server.

You can do this in code, with the stored procedure sp_addlinkedserver. You can learn more about it here.

Or, you can do it through SSMS:

Answer 2

I hope the below information will help you: Using SQL Server Management Tools you can use the Import Feature.

1. Connect to your SQL instance server.
2. Select your database schema.
3. Right click Tasks > Import.
4. and follow wizard instructions.