Comparing two tables Rows
In my project, I need to identify the data difference between two tables.Before that I want to give some more information about that requirement. I have two databases with different columns in the base table. By using the base table, we will create the remaining tables. If the columns are different in that base table, then entire database will be very complex for data migration.So we need to check that base table data before the migration.
Let’s consider the Customer_2000 and sales_2005 database and tblCustomerDetails and tblCustomerPersonDetails are base tables in the respective database. We have some unique fields [ID],[Name] and Location. By using those fields, we should identify the difference between the two tables.
Following query will give the required output as per my requirement:
SELECT MIN(TableName) as TableName, [ID],[Name], Location
FROM
(
SELECT 'Customer_2000.tblCustomerDetails' AS TableName, a.CustomerID,a.CustomerName, a.Location
FROM [EMS4_V7_HMI_Export].[dbo].[SK_APC_PI_Model_Overview] a
union all
SELECT 'Sales_2005.tblCustomerPersonDetails' AS TableName,b.SalesID,b.SalesPersonName, b.Location
FROM [EMS4_contract_DB_Phase_Four].[dbo].[SK_APC_PI_Model_Overview] b
) tmp
GROUP BY [ID],[Name], Location
Having Count(*) = 1
Order by [Name]
|
No responses found. Be the first to respond and make money from revenue sharing program.
|