[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

SQL Server: ways to compare data between large tables and view

Need to compare data between large tables and views. Databases are on different servers.  One has tables, the other has views derived from the 3rd database on the same server.
EXCEPT on original tables and views takes forever.

Thank you in advance.
0
quasar_ee
Asked:
quasar_ee
3 Solutions
 
Jim P.Commented:
Is it just the primay key(s) or is it deeper in?

For just a PK you can use left joins. For example:
SELECT srvr1.PK, srvr2.PK, srvr1.fld1, srvr1.fld2, srvr1.fld3 ....
FROM [Svr1\Instance].[DBName].dbo.TblName srvr1
LEFT  JOIN [Svr2\Instance].[DBName].dbo.TblName srvr2
     ON srvr1.PK = srvr2.PK
WHERE srvr2.PK is null /**  or is not null  **/

Open in new window


If you want to check specific fields then add it in the ON clause.
SELECT srvr1.PK, srvr2.PK, srvr1.fld1, srvr1.fld2, srvr1.fld3 ....
FROM [Svr1\Instance].[DBName].dbo.TblName srvr1
LEFT  JOIN [Svr2\Instance].[DBName].dbo.TblName srvr2
     ON srvr1.PK = srvr2.PK
     AND srvr1.fld1= srvr2.fld1
WHERE srvr2.PK is null /**  or is not null  **/
AND      srvr2.fld1 is null  /**  or is not null  **/

Open in new window

0
 
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

You can use EXISTS

Select * from Table1 b where srvr1.PK not exists (select srvr2.PK from view a where a.srvr2.PK=b.srvr1.PK)

Open in new window


Which will give you the records which are in table but not in view

Same way you can find records which are in view but not in Table

Hope this would help
0
 
David ToddSenior DBACommented:
Hi,

Would Red Gate Data compared be easier to use/better performance?

HTH
  David
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now