How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)

The remote SQL Server IP is: 129.113.271.58 (I've changed the real IP) The name of the database on this remote server is: 'ku.univ', and the remote table name I would like to update is 'dbo.Courses' (the table has the same fields as the local one).

I use SQL Server Express Edition

Please help!!

Thank you in advance

Michael

Any ideas?

I'm really stuck here - I am able to connect to the remote server and browse the database, but how do I make an INSERT query to the remote table from my local table?

In Access you could just put any table (local and linked) in one query - there has to be a way to do it in SQL Server

Thanx

|||

There are a couple of options for you. You could use BCP to export the table on the local server to a text file, then use the same utility to insert into the remote server. You can also use a linked server.

To setup a linked server run these commands from sqlcmd, replace the appropriate parameters:

exec sp_addlinkedserver 'RemoteServerName', N'SQL Server'
exec sp_addlinkedsrvlogin 'RemoteServerName',@.locallogin=NULL /*any login*/, @.rmtuser = 'remoteusername', @.rmtpassword='rmtpassword'

Then you will be able to run a query such as this when connected to your local server:

insert [remoteserver].[ku.univ].[dbo].[Tableneme] (columns...)

select columns... from localtable

I'm assuming here that the remote server is also SQL Server. If not you'll need to modify the sp_addlinkedserver call to specify the proper connection string.