Tables Compare

Transact-SQL
1

Hi.

I have 2 different DB schemas, and i want to write a procedure to compare difference in the 2 tables based on the count, with specific 2 columns and list the difference in a new table. This is based on the difference in number.

e.g table1: Column Name: John, Thomas, Peter
table2: Column Name: John, Thomas, Lucas, Mike

The result should state that the count difference is 2( Lucas & Mike are not in Table1) and List the difference in a new table.

2

Something like this perhaps:



insert into new_table (column_name)
select column_name
  from table1 as a
 where not exists (select 1
                     from table2 as b
                    where b.column_name=a.column_name
                  )
;
3

create table car
(
CarName varchar(50),
CarSize int,
CarColor varchar(50)
)

create table Newcar
(
CarName varchar(50),
CarSize int,
CarColor varchar(50),
CarShape varchar(50),
CarType varchar(50)
)

with cte as(
select c2.table_name,c2.COLUMN_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c2
where table_name='Newcar'
and c2.COLUMN_NAME not in (select column_name
from [INFORMATION_SCHEMA].[COLUMNS]
where table_name='car')
)

select count(*) from cte