Matching tables between a SQL Server database and a Synapse Analytics database

Question

Hi,

I've implemented a Synapse Analytics dwh by migrating a SQL Server dwh, passing for an intermediate ADLS gen2 storage.

Inside the Management Studio tool I'd like to create some queries with the join clause in order to evaluate the data matching between SQL Server tables against the corresponding Synapse Analytics tables.

Any suggests to me, please, if it is possible obviously? Thanks

Answer 1

It's still grossly unclear what you want to do.

Generally, to compare two tables with the same schema, the pattern is:

SELECT *
FROM  A
FULL JOIN B ON A.keycol1 = B.keycol1
           AND A.keycol2 = B.keycol2
WHERE  NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)

But if one table is in SQL Serve on-prem and the other is Synapse Serverless pool, you would need a linked server. And making such a comparison over a linked server where data has to be dragged across the network - well, it will not be fast.

Answer 2

Hi pmscorca ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As I understand your requirement , you are trying to match the data between SQL server and azure synapse datawarehouse. Please correct me if my understanding has any gap.

You can use Mapping dataflow and create two source connections one for each of the sources, and use Join transformation to find out the matching records based on the common column.

Related documentations: Join transformation in mapping data flow

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou