Join across instances - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Join across instances

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2009-09-04 : 08:36:40

One of our developers has developed scripts that work to structure the data in a target environment and copy new control type data from a source environment while maintaining the transaction type data from the target environment.

Here is an example

INSERT INTO [SignoffQualification]                              ([QualifierSignoffTypeID]
           ,[Qualifier])
(

SELECT T2.SignoffTypeID, q.Qualifier 
FROM ([MYDB].[dbo].[SignoffQualification] q
	left join [MYDB].[dbo].[SignoffType] T1 on q.QualifierSignoffTypeID = T1.SignoffTypeID)
	left join SignoffType T2 on T1.SignoffTypeName = T2.SignoffTypeName
)

The question I have is whether this technique can be used when the two databases are on different servers?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 08:47:05

This should work using linked server...

No, you're never too old to Yak'n'Roll if you're too young to die.

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-09-04 : 08:47:37

You will need to set up Linked servers or use the opendatasource function. Either way the query will need to be slightly modified depending on which you use.
Setting up Linked server is more of a permanent solution opendatasource is mostly for ad hoc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources