Cross Instance Queries

Using the toolbar at the top of the Object Explorer pane, you can connect SQL 4 CDS to multiple D365 instances. Each query tab is connected to one of those instances, but can also reference data from other connected instances as well.

Each connected instance can be referred to in your SQL query as if it was a different database:

SELECT uat.name, prod.name
FROM   [UAT D365].dbo.account AS uat
       INNER JOIN [PROD D365].dbo.account AS prod
       ON uat.accountid = prod.accountid
WHERE  uat.name <> prod.name

This example assumes that you have two instances connected, one called "UAT D365" and one called "PROD D365". This will find accounts in the two different instances that have the same ID but different names.

Copying data

You can use cross-instance queries to copy data from one instance to another, for example:

INSERT INTO account (name)
SELECT name
FROM   [UAT D365].dbo.account

In this example the account data from the UAT system will be copied into the instance that the current query tab is connected to, as no specific target instance has been specified.