How to identify the PK values are same in 2 databases

Hi Experts,

  I've two databases. Considere DB1 and DB2. For list of common tables I would like to perform below activity:

   If the PrimaryKey values is different I would like to know the tables. Say for example the tEmployee table in DB1 and DB2. In DB1 the EmpID is having value as 1, 2..... n. But in DB2 the EmpID values as 100001, 100002.... 10000n. Then in result of my query I would like to get tEmployee and such kind of other tables. How to write query for it?


Please note that both DB1 and DB2 are in same server. Please assist.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
First you have to define what different means.  What are you comparing if it's not the ID value?  Are you just comparing the min and max values of the ID?
0
Easwaran ParamasivamAuthor Commented:
If any one of the PK value is not present in another table. As I mentioned in the question EmployeeID 1 is present in DB1 but 1 is not present in DB2. Then I would like to get the table name as output.
0
PortletPaulfreelancerCommented:
This may seem counter-intuitive, but a good method for this is to use UNION ALL (and then group that result). Below is a simple simulation. Substitute "DB1_" with "[DB1]." and so on for your cross-database need.
    CREATE TABLE DB1_tEmployee
    	([ID] int)
    ;
    INSERT INTO DB1_tEmployee
    	([ID])
    VALUES
    	(1),    	(2),    	(3), --<< not in DB2
    	(4),    	(5),    	(6),
    	(8),    	(9)
    ;
    
    CREATE TABLE DB2_tEmployee
    	([ID] int)
    ;
    INSERT INTO DB2_tEmployee
    	([ID])
    VALUES
    	(1),    	(2),    	(4),
    	(5),    	(6),    	(7), --<< not in DB1
    	(8),    	(9)
    ;

**Query 1**:

    SELECT
            ID
          , max(SOURCE) AS SOURCE
    FROM (
          SELECT 'DB1' AS SOURCE, ID FROM DB1_tEmployee
          UNION ALL
          SELECT 'DB2' AS SOURCE, ID FROM DB2_tEmployee
          ) AS derived
    GROUP BY
            ID
    HAVING
           count(*) = 1
    ;    	

**[Results][2]**:
    
    | ID | SOURCE |
    |----|--------|
    |  3 |    DB1 |
    |  7 |    DB2 |

  [1]: http://sqlfiddle.com/#!3/d24a0/1

Open in new window

for more detail on this approach see: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
{+ an edit, sorry}
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

sachitjainCommented:
Interesting problem. See if following code helps:

declare @db1 table (TblName varchar(50), PrimaryKeyCol varchar(50))
declare @db2 table (TblName varchar(50), PrimaryKeyCol varchar(50))
declare @common table (SNO int identity(1,1), TblName varchar(50), PrimaryKeyCol varchar(50))
declare @MismatchedTables table (TblName varchar(50))
declare @i int, @result1 int, @result2 int, @result3 int, @cnt int
declare @tbl1 varchar(50), @tbl2 varchar(50), @query nvarchar(max), @join nvarchar(max), @cols nvarchar(max)

insert into @db1
select t.[name], c.[name]
from Database1.sys.tables t
      inner join Database1.sys.indexes i on i.[object_id] = t.[object_id]
      inner join Database1.sys.index_columns ic on ic.[object_id] = i.[object_id]
      inner join Database1.sys.columns c on ic.column_id = c.column_id and ic.[object_id] = c.[object_id]
where i.[name] like 'PK%'

insert into @db2
select t.[name], c.[name]
from Database2.sys.tables t
      inner join Database2.sys.indexes i on i.[object_id] = t.[object_id]
      inner join Database2.sys.index_columns ic on ic.[object_id] = i.[object_id]
      inner join Database2.sys.columns c on ic.column_id = c.column_id and ic.[object_id] = c.[object_id]
where i.[name] like 'PK%'

insert into @common
select d1.TblName, d1.PrimaryKeyCol
from @db1 d1
      inner join @db2 d2 on d1.TblName = d2.TblName and d1.PrimaryKeyCol = d2.PrimaryKeyCol
      
set @i = 1
select @cnt = COUNT(*) from @common
set @tbl1 = ''
while @i <= @cnt
begin
      set @tbl2 = null
      select @tbl2 = tblName from @common where SNO = @i and TblName <> @tbl1
      if @tbl2 is not null
      begin
            set @cols = null
            select @cols = (Case when @cols is null then '' else @cols + ', ' end) + 'd1.' + PrimaryKeyCol
            from @common where TblName = @tbl2
            set @query = 'select @result1 = count(*) from (select distinct ' + @cols + ' from Database1.dbo.' + @tbl2 + ' d1) l'
            print @query
            execute sp_executesql @query, N'@result1 int output', @result1 output
            
            set @cols = null
            select @cols = (Case when @cols is null then '' else @cols + ', ' end) + 'd1.' + PrimaryKeyCol
            from @common where TblName = @tbl2
            set @query = 'select @result2 = count(*) from (select distinct ' + @cols + ' from Database2.dbo.' + @tbl2 + ' d1) l'
            print @query
            execute sp_executesql @query, N'@result2 int output', @result2 output
            
            set @join = null
            select @join = (Case when @join is null then '' else @join + ' and ' end)
                  + 'd1.' + PrimaryKeyCol + ' = '
                  + 'd2.' + PrimaryKeyCol
            from @common where TblName = @tbl2
            set @query = 'select @result3 = count(*)
                              from (select distinct ' + @cols + '
                                          from Database1.dbo.' + @tbl2 + ' d1 inner join Database2.dbo.' + @tbl2 + ' d2 on ' + @join + ') l'
            print @query
            execute sp_executesql @query, N'@result3 int output', @result3 output

            if (@result1 <> @result3) or (@result2 <> @result3)
            begin
                  insert into @MismatchedTables values (@tbl2)
            end
            
            set @tbl1 = @tbl2
      end      
      set @i = @i + 1
end

select * from @MismatchedTables
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachitjainCommented:
In above logic, you need to replace Database1 and Database2 with your corresponding database names
0
Scott PletcherSenior DBACommented:
UNION ALL with GROUPing will be extraordinarily high overhead (SQL will have to sort all the combined rows from both tables), so I would strongly suggest not using it.

Instead, use a standard FULL OUTER JOIN:

SELECT
    'Row in ' + CASE WHEN t1.key_col IS NULL THEN 'DB2' ELSE 'DB1' END + 'only' AS message,
    COALESCE(t1.key_col, t2.key_col) AS key_col,
    COALESCE(t1.data_col1, t2.data_col1) AS data_col1,
    COALESCE(t1.data_col2, t2.data_col2) AS data_col2,
    ...
FROM DB1.dbo.table_name t1
FULL OUTER JOIN DB2.dbo.table_name t2 ON
    t2.key_col = t1.key_col -- + 100000 or other differential amount, if needed
WHERE
    t1.key_col IS NULL OR
    t2.key_col IS NULL
0
Easwaran ParamasivamAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.