How to refer to another Database

[phil22046]

I have recovered data (Database Trackit, table tasks) on my desktop machine running SQL 2005 Developer. The data needs to be inserted into the (Database Trackit, table tasks table) on the SQL1 Server. I want to insert all records from my desktop onto the Sql1 Server. I did Register the server so that I can now 'see it' from my desktop SQL server. I just don't know how to refer to the other database to do the insert.

I think it will be something like this:

INSERT INTO \\sh-sql1\Trackit.tasks FROM \\localmachine\Trackit.tasks

But I am sure that is not exactly correct. Please advise.

 

[ptheriault]

From either your desktop server or server 1 create a link server. Then you can reference the insert with the 4 part naming convention.


INSERT INTO server.db.schema.object
FROM table



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Let me give you a bit more detail.
From SSMS on your PC that has 2005 developer edition.

Open the Server Object folder and select Linked Servers.
Right click on link server and select new link server.
Type the server name and select SQL Server as the Server type.
Click on the security option.
Select Be made using this security context.
Add a valid login and password.

Test link server.

Open a query window.
Create a simple select to a database on Server1 like this.

Select * from Server1.db.schema.table

If it works run your insert.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

OK I don't know what a Link server is. I do have the SQL1 server registered with the local desktop server.

 

[ptheriault]

A link server is a way to access data between two servers without using SSIS. Just because you have a server registered from SSMS doesn't mean the two servers can communicate. You need to add the link server so they can do that.

If you follow the steps I've outlined above you should be able to insert your data in Server1 from your PC.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

We named the other server SH-SQL1. Whenever I refer to SH-SQL1.db.schema.table, the '-' is a problem, I get "Incorrect syntax near '-'.

 

[ptheriault]

Do this.

SELECT
FROM [SH-SQL1].[db].schema.table

- Paul
- If at first you don't succeed, find out if the loser gets anything.