asked
Db link in sqlserver 2005
I have three databases in sql server 2005. I want to retrieve values of the another database table into one database. Say For an example three databases are there DB1,DB2 and DB3..In Db1 I want to acces the DB2 table..Please explain me what is the syntax for Db link and how to use it in Stored procedure. Thanks, Sugan.
Assuming that you are accessing a local DB (Not remote database) For accessing table of DB2 From DB1 use the following query
-- If your DB is located on the same server then try the following code
SELECT FROM ..
eg : SELECT ID FROM DB2.dbo.Employee
Here is how you can retrieve the data from DB2.
Select FieldName From DB2.dbo.DB2TableName
Get the data from DB2 and insert into DB1.
Insert Into DB1.dbo.DB1TableName (FieldName)
Select FieldName From DB2.dbo.DB2TableName
Select FieldName From DB2.dbo.DB2TableName
Get the data from DB2 and insert into DB1.
Insert Into DB1.dbo.DB1TableName (FieldName)
Select FieldName From DB2.dbo.DB2TableName
A good way to do this is by using a synonym object. This allows you to decouple your logic from the database name. So, for example, to access db2.dbo.table - You could: CREATE SYNONYM dbo.tableInDB2 FOR db2.dbo.table Then SELECT * FROM dbo.tableInDB2 This way, your stored procedure only depends on the locally created synonym, and if the name of DB2 changes, or it moves to another server, then you only need to redefine the synonym - and not re-write all your procedures that use it.
