Hi,
I am a novice server 2000 guy. I have two databases running under one
server. One is in production and another one was created by my
predecessor to retire production database and he might have added some
extra tables and fields in the new database. My job is to make sure
that whatever in the current production database is in the new
database. How can I compare the tables and if any data doesn't match
print that raw?
I tried few select ...join but did not work...
Thank you in advance...
ChorI would just like to elaborate more on my quetion...
let's say I have two identical tables with different data...
pubs.dbo.authors has 23 records
tempdb.dbo.authors has 20 records
I want to see the difference. Hope this make sense|||3rd party tool. SQL Compare by RedGate. Free 30 day
trial...www.red-gate.com
--
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147788619.613730.8390@.j33g2000cwa.googlegroups.com...
> Hi,
> I am a novice server 2000 guy. I have two databases running under one
> server. One is in production and another one was created by my
> predecessor to retire production database and he might have added some
> extra tables and fields in the new database. My job is to make sure
> that whatever in the current production database is in the new
> database. How can I compare the tables and if any data doesn't match
> print that raw?
> I tried few select ...join but did not work...
> Thank you in advance...
> Chor
>|||1 way would be :-
SELECT * FROM pubs.dbo.authors where UniqueID NOT IN (SELECT UniqueID FROM
tempdb.dbo.authors)
There are far more elaborate solutions and i'm sure i'll get flamed for
using NOT IN rather than EXISTS...
--
HTH. Ryan
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147791030.151031.288430@.u72g2000cwu.googlegroups.com...
>I would just like to elaborate more on my quetion...
> let's say I have two identical tables with different data...
> pubs.dbo.authors has 23 records
> tempdb.dbo.authors has 20 records
> I want to see the difference. Hope this make sense
>|||SELECT a.ID, a.CheckSum
>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...|||here are 3 more ways
select * from tempdb..authors t2 right join pubs..authors t1 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 left join tempdb..authors t2 on
t1.au_id =t2.au_id
where t2.au_id is null
select * from pubs..authors t1 where not exists(select * from
tempdb..authors where t1.au_id =au_id)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||SELECT a.ID, a.CheckSum
>From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM pubs.dbo.authors ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM tempdb.dbo.authors ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
I tried this query but it doesn't return last three missing records(I
deleted those records from tempdb.dbo.rad_oltp to check if they come
from pubs.dbo.authors table up) ... but nothing returned...
My mistake.. there is a typo .. it is tempdb.dbo.authors|||Here we go
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Here is the complete script that I used to create the authors2 table
and modify/add records to test
--let's copy over 20 rows to a table named authors2
select top 20 * into tempdb..authors2 from pubs..authors
--update 5 records by appending X to the au_fnam
set rowcount 5
update tempdb..authors2
set au_fname =au_fname +'X'
set rowcount 0
--let's insert a row that doesn't exist in pubs
insert into tempdb..authors2
select '666-66-6666', au_lname, au_fname, phone, address, city, state,
zip, contract
from tempdb..authors2
where au_id ='172-32-1176'
--The BIG SELECT
--Not in Pubs
select 'DoesNotExistOnProduction',t2.au_id from pubs..authors t1
right join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t1.au_id is null
union all
--Not in Temp
select 'DoesNotExistInStaging',t1.au_id from pubs..authors t1 left
join tempdb..authors2 t2 on t1.au_id =t2.au_id
where t2.au_id is null
union all
--Data Mismatch
select 'DataMismatch', t1.au_id from( select BINARY_CHECKSUM(*) as
CheckSum1 ,au_id from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id from tempdb..authors2) t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thank you so much.. that worked!! :)
You made my day:):):)|||No problem
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||One more question... I tried using more then one column to check the
data mismatch...
--Data Mismatch
select 'DataMismatch', t1.au_id, t1.au_lname, t1.au_fname from( select
BINARY_CHECKSUM(*) as
CheckSum1 ,au_id, au_lname, au_fname from pubs..authors) t1
join(
select BINARY_CHECKSUM(*) as CheckSum2,au_id, from tempdb..authors2)
t2
on t1.au_id =t2.au_id
Where CheckSum1 <> CheckSum2
did not work as I intended...|||Sorry what I just posted was a stupid question... my mistake.....
ddddduhhhh|||CHECKSUM(*) will use all columns
Denis the SQL Menace
http://sqlservercode.blogspot.com/
sql
Ahsukal wrote: