-
-
Save naradae/4df8a1329d5f7a1912d82c48c3d0c43f to your computer and use it in GitHub Desktop.
Cross-server queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
- Replace OtherServerName with a real server name | |
*/ | |
USE [master] | |
GO | |
EXEC master.dbo.sp_addlinkedserver @server = N'OtherServerName', @srvproduct=N'SQL Server' | |
GO | |
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'OtherServerName', @locallogin = NULL , @useself = N'True' | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
- Note server and database references | |
- Note COLLATE keyword is used to normalize string collation and allow equality between the databases | |
- If there is a collation mismatch, then you may receive an error message similar to this: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation. | |
*/ | |
SELECT sourceCustomer.[No_] as [Customer No_] | |
, sourceCustomer.Name as [Name in Source Database] | |
, targetCustomer.Name as [Name in Current Database] | |
, * | |
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer | |
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer | |
ON sourceCustomer.[No_] COLLATE Latin1_General_CI_AS = targetCustomer.[No_] COLLATE Latin1_General_CI_AS |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
- Note server and database references | |
*/ | |
SELECT sourceCustomer.[No_] as [Customer No_] | |
, sourceCustomer.Name as [Name in Source Database] | |
, targetCustomer.Name as [Name in Current Database] | |
, * | |
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer | |
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer | |
ON sourceCustomer.[No_] = targetCustomer.[No_] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
- Note server and database references | |
*/ | |
UPDATE targetCustomer | |
SET Name = sourceCustomer.Name | |
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer | |
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer | |
ON sourceCustomer.[No_] = targetCustomer.[No_] | |
WHERE sourceCustomer.Name <> targetCustomer.Name |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment