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.
