I am using MS SQL Server 2014. I need to compare data from two queries on separate servers.
我正在使用MS SQL Server 2014.我需要比较来自不同服务器上的两个查询的数据。
Can someone help please?
有人可以帮忙吗?
Many thanks
2 个解决方案
#1
1
You need to take different approach depending on many situations:
您需要根据许多情况采取不同的方法:
If the two servers are visible each other and you can create a database link
如果两个服务器彼此可见,则可以创建数据库链接
Create a database link and use it to compare the data
创建数据库链接并使用它来比较数据
If the two servers are not visible or you can't create a database link
如果两个服务器不可见或您无法创建数据库链接
1) Extract the data sorted from both databases and compare them row by row with a simple text comparator. You need to extract the raw data in the same order of columns and with the same format for columns that need convertion to test.
1)提取从两个数据库中排序的数据,并使用简单的文本比较器逐行比较它们。您需要以相同的列顺序提取原始数据,并为需要转换为测试的列使用相同的格式。
2) Use an ETL tool (Pentaho for example) to import both tables in a third database and compare them directly
2)使用ETL工具(例如Pentaho)在第三个数据库中导入两个表并直接比较它们
3) Manually import both tables in a third database and compare them directly
3)手动导入第三个数据库中的两个表并直接比较它们
#2
0
I ran both queries separately. Outputted to Excel and used vba code to compare.
我分别运行了两个查询。输出到Excel并使用vba代码进行比较。
#1
1
You need to take different approach depending on many situations:
您需要根据许多情况采取不同的方法:
If the two servers are visible each other and you can create a database link
如果两个服务器彼此可见,则可以创建数据库链接
Create a database link and use it to compare the data
创建数据库链接并使用它来比较数据
If the two servers are not visible or you can't create a database link
如果两个服务器不可见或您无法创建数据库链接
1) Extract the data sorted from both databases and compare them row by row with a simple text comparator. You need to extract the raw data in the same order of columns and with the same format for columns that need convertion to test.
1)提取从两个数据库中排序的数据,并使用简单的文本比较器逐行比较它们。您需要以相同的列顺序提取原始数据,并为需要转换为测试的列使用相同的格式。
2) Use an ETL tool (Pentaho for example) to import both tables in a third database and compare them directly
2)使用ETL工具(例如Pentaho)在第三个数据库中导入两个表并直接比较它们
3) Manually import both tables in a third database and compare them directly
3)手动导入第三个数据库中的两个表并直接比较它们
#2
0
I ran both queries separately. Outputted to Excel and used vba code to compare.
我分别运行了两个查询。输出到Excel并使用vba代码进行比较。