Comparing Tables in SQL Server 2k

I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.

I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.

Thanks!assuming that both columns are part of the primary key, i.e. not null

rows in table1 that don't exist in table2 --
select t1.PartID
, t1.RaceID
from table1 as t1
left outer
join table2 as t2
on t1.PartID = t2.PartID
and t1.RaceID = t2.RaceID
where t2.PartID is null

rows in table2 that don't exist in table1 --
select t2.PartID
, t2.RaceID
from table1 as t1
right outer
join table2 as t2
on t1.PartID = t2.PartID
and t1.RaceID = t2.RaceID
where t1.PartID is null|||That's okay, but I think it needs more cowbell:

select table1.PartID,
table1.RaceID,
table2.PartID,
table2.RaceID
from table1
full outer join table2
on table1.PartID = table2.PartID
and table1.RaceID = table2.RaceID
where table1.PartID is null
or table2.PartID is null

I just gotta have more cowbell!|||cowbell? is that unique to sql server? i never heard of it|||Thanks. That allowed me to find the discrepancies. Does anyone know where I can find a tutorial on joins (inner, outer, right, left, etc)?

Thanks!|||BOL (Books online), the topic "Types of Joins".|||thanks for your help!|||Cowbell:

http://209.151.80.80/media/cowbell.wmv|||hilarious clip

i don't understand the relevance to full outer joins, but that could just be me

:)|||http://www.urbandictionary.com/define.php?term=More+Cowbell&r=s&pos=1

Also: The addition of something which contributes nothing of value. ie: the full outer join as opposed to the separate left joins.|||What about creating a UNION view consisting only of the two key attributes? SQL Server will prepare a nice QEP for quick selection; if you have a second view, that queries on NULL values for both fields in that view it will be faster than any outer join, maybe ;-)|||I got a fever! And the only prescription is MORE COWBELL!|||a UNION view!! why didn't i think of that!!

select PartID
, RaceID
, min(source) as source_table
from (
select PartID
, RaceID
, 'table1' as source
from table1
union all
select PartID
, RaceID
, 'table2'
from table2
) as u
group
by PartID
, RaceID
having count(*) between 0 and 1
note how MIN() will tell you which of the tables the unmatched row comes from

sweet, eh?

the HAVING clause won't ever actually find count(*)=0, i just threw that in there because of the cowbells

:) :) :)|||And I thought I knew a little something about sql queries....

I guess that is accurate- a LITTLE something...

This is a very informational and helpful thread!

Thanks to all--although I am still a little in the dark re: this cowbell thing...

Comparing Tables and Columns Between Two Databases

Hi,

I have a two databases that may be similar but are not identical.I would like to compare the table and column names of the two databases to find out where they differ.Are there any tools that would help me in this task?In Oracle I would compare the meta data of the two databases in a select clause.Can we do the same thing in MS SQL?

Thanks in advance,

Dave

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054 should get you what you are looking.

Comparing data from two identical databases

Since splitting a large SQL 7.0 database off into several smaller but structurally identical ones based on year, I've discovered that there is some data missing from the new database
I need to find out what data in the old database is not in the new database, and insert it into the new database
Can anyone help
Thanks in advance
John SteenTake a look at SQLCompare from Red Gate.
www.sqlcompare.com
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"John Steen" <anonymous@.discussions.microsoft.com> wrote in message
news:C350518B-4F74-4E97-B5E1-0B45C4797001@.microsoft.com...
> Since splitting a large SQL 7.0 database off into several smaller but
structurally identical ones based on year, I've discovered that there is
some data missing from the new database.
> I need to find out what data in the old database is not in the new
database, and insert it into the new database.
> Can anyone help?
> Thanks in advance.
> John Steen|||check out www.dbghost.com
>--Original Message--
>Since splitting a large SQL 7.0 database off into several
smaller but structurally identical ones based on year,
I've discovered that there is some data missing from the
new database.
>I need to find out what data in the old database is not
in the new database, and insert it into the new database.
>Can anyone help?
>Thanks in advance.
>John Steen
>.
>|||Thanks, Mark. I may look into it that tool later, but I need a query ASAP that will identify the data in the old DB that's missing from the new DB, and then insert that data.
I've tried this, but it returns 0 rows, and I know there is discrepancy:
SELECT * FROM oldDB.dbo.table1
WHERE NOT EXISTS (SELECT * from newDB.dbo.table1)
My query skills are minimal, so I don't know what's wrong with this query. But I would appreciate any and all help. I'm desperate!
Thanks,
John
-- mark baekdal wrote: --
check out www.dbghost.com
>--Original Message--
>Since splitting a large SQL 7.0 database off into several
smaller but structurally identical ones based on year,
I've discovered that there is some data missing from the
new database.
>>I need to find out what data in the old database is not
in the new database, and insert it into the new database.
>>Can anyone help?
>>Thanks in advance.
>>John Steen
>.
>

Comparing data across two databases

Hi,
I have two identical databases (Production and Testing) sitting in different
servers (Server A and B) .
Is there anyway to compare data between these databases at all? More
particularly, I want to compare data in 20 tables from above databases and
identify any difference.
Is this possible at all? Can someone help please?
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Harish,
There are some great 3rd party tools that can accomplish this. I use the
ones offered at http://www.red-gate.com.
Shaun McDonnell
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:70D2B1D3-2A33-45F0-A632-B1B4BEAAAA82@.microsoft.com...
> Hi,
> I have two identical databases (Production and Testing) sitting in
> different
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/|||Look up the tablediff utility in Books Online...
..."TableDiff was intended for replication but can easily apply to any
scenario where you need to compare data and/or schema"
([url]http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx[/u
rl])
"Harish Mohanbabu" wrote:

> Hi,
> I have two identical databases (Production and Testing) sitting in differe
nt
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/|||You might want to use AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com/
- you can easily compare data in databases located in different
servers. SCT has lot of fine-tuning options, like: ignoring selected
columns or specifying comparison method (record-by-record or primary
key/index comparison).
Comparison "sessions" can be saved and re-played later without need of
re-entering the parameters. Command line parameter can be used to
fully automate comparisons.
Dariusz Dziewialtowski.sqlsql

Comparing data across two databases

Hi,
I have two identical databases (Production and Testing) sitting in different
servers (Server A and B) .
Is there anyway to compare data between these databases at all? More
particularly, I want to compare data in 20 tables from above databases and
identify any difference.
Is this possible at all? Can someone help please?
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
Harish,
There are some great 3rd party tools that can accomplish this. I use the
ones offered at http://www.red-gate.com.
Shaun McDonnell
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:70D2B1D3-2A33-45F0-A632-B1B4BEAAAA82@.microsoft.com...
> Hi,
> I have two identical databases (Production and Testing) sitting in
> different
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/
|||Look up the tablediff utility in Books Online...
..."TableDiff was intended for replication but can easily apply to any
scenario where you need to compare data and/or schema"
(http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx)
"Harish Mohanbabu" wrote:

> Hi,
> I have two identical databases (Production and Testing) sitting in different
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/
|||You might want to use AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com/
- you can easily compare data in databases located in different
servers. SCT has lot of fine-tuning options, like: ignoring selected
columns or specifying comparison method (record-by-record or primary
key/index comparison).
Comparison "sessions" can be saved and re-played later without need of
re-entering the parameters. Command line parameter can be used to
fully automate comparisons.
Dariusz Dziewialtowski.

Comparing data across two databases

Hi,
I have two identical databases (Production and Testing) sitting in different
servers (Server A and B) .
Is there anyway to compare data between these databases at all? More
particularly, I want to compare data in 20 tables from above databases and
identify any difference.
Is this possible at all? Can someone help please?
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Harish,
There are some great 3rd party tools that can accomplish this. I use the
ones offered at http://www.red-gate.com.
Shaun McDonnell
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:70D2B1D3-2A33-45F0-A632-B1B4BEAAAA82@.microsoft.com...
> Hi,
> I have two identical databases (Production and Testing) sitting in
> different
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/|||Look up the tablediff utility in Books Online...
..."TableDiff was intended for replication but can easily apply to any
scenario where you need to compare data and/or schema"
(http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx)
"Harish Mohanbabu" wrote:
> Hi,
> I have two identical databases (Production and Testing) sitting in different
> servers (Server A and B) .
> Is there anyway to compare data between these databases at all? More
> particularly, I want to compare data in 20 tables from above databases and
> identify any difference.
> Is this possible at all? Can someone help please?
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/|||You might want to use AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com/
- you can easily compare data in databases located in different
servers. SCT has lot of fine-tuning options, like: ignoring selected
columns or specifying comparison method (record-by-record or primary
key/index comparison).
Comparison "sessions" can be saved and re-played later without need of
re-entering the parameters. Command line parameter can be used to
fully automate comparisons.
Dariusz Dziewialtowski.

Compare two queries to see if their results are identical

Scenario - I would like to compare two queries to find out if their results
are identical. The first query returns all the values stored in an invoice
table for a given invoice no. The second query returns all the values
stored in a table holding booking information.
E.g.
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
InvoiceDetails WHERE InvoiceNo = 12345
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
BookingDetails WHERE BookingNo = 20
I need to know whether the results from the two queries are identical. One
way I can think of doing this is to perform two EXISTS queries, the first
one finding a row that does not exist in InvoiceDetails that does exist in
BookingDetails and a second query that finds a row that does not exist in
BookingDetails that does in InvoiceDetails. Then if one or the other tests
finds a row the answer is they are different otherwise they are the same.
But is there a better way to perform this test - can be be done is one test?
Thanks.One way is to do a full join and then look for NULLs on either side.
Another method is to UNION the two queries and in the result GROUP BY
<all columns> HAVING COUNT(*)=1 to show any mismatches. This second
method has the advantage that you can compare on the basis of NULL=NULL
if you wish.
David Portas
SQL Server MVP
--|||The first way is to create a cursor with your first query and within to
compare it with your second result, as you specify it with IF EXISTS.
You should also do the opposite operation with the second resultset.
But you should consider that cursor are not efiiciency for large table. So
you could also create tempory table from you resulstet and compare them
trough join operation,
Laurent
"Chris" <cw@.community.nospam> a crit dans le message de news:
eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||Try:
if exists (
select *
from InvoiceDetails as a inner join BookingDetails as b
on a.InvoiceNo = 12345 and b.BookingNo = 20
and a.Quantity = b.Quantity
and a.[Description] = b.[Description]
and a.UnitPrice = b.UnitPrice
and a.TaxPercentage = b.TaxPercentage
and a.Tax = b.Tax
and a.Price = b.Price
)
print 'identical'
else
print 'not identical'
go
AMB
"Chris" wrote:

> Scenario - I would like to compare two queries to find out if their result
s
> are identical. The first query returns all the values stored in an invoic
e
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical. On
e
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other test
s
> finds a row the answer is they are different otherwise they are the same.
> But is there a better way to perform this test - can be be done is one tes
t?
> Thanks.
>
>|||Can't you do a FULL OUTER JOIN and see if either side is NULL?
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||CREATE TABLE #T1(i int, j int)
CREATE TABLE #T2(i int, j int)
insert into #t1 values(1,2)
insert into #t2 values(1,2)
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- identical
--
1
update #t2 set j=3
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- different
--
2
drop table #t1
drop table #t2|||USE LRUKUVL
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2
CREATE TABLE
#Table1
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
CREATE TABLE
#Table2
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
INSERT INTO #Table1
SELECT 1 , 4 , 'A'
UNION ALL SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , NULL , 'C'
UNION ALL SELECT 4 , 1 , 'D'
UNION ALL SELECT 5 , 1 , 'D'
UNION ALL SELECT 7 , 5 , NULL
INSERT INTO #Table2
SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , 2 , 'C'
UNION ALL SELECT 4 , 1 , NULL
UNION ALL SELECT 5 , 1 , 'E'
UNION ALL SELECT 6 , 1 , 'F'
UNION ALL SELECT 7 , 5 , NULL
/* return records that are different */
/* only id=2 and id=7 are equal - others are returned */
/* write a UDF to compaire a.data = b.data checking for equality, and is
nulls differences */
SELECT
*
FROM
#Table1 a
FULL OUTER JOIN
#Table2 b
ON
a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
OR
( a.data1 IS NULL AND b.data1 IS NOT NULL )
OR
( a.data1 IS NOT NULL AND b.data1 IS NULL )
OR
( a.data1 != b.data1 )
OR
( a.data2 IS NULL AND b.data2 IS NOT NULL )
OR
( a.data2 IS NOT NULL AND b.data2 IS NULL )
OR
( a.data2 != b.data2 )
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
results
> are identical. The first query returns all the values stored in an
invoice
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
One
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other
tests
> finds a row the answer is they are different otherwise they are the same.|||Thanks to everyone for the replies
Chris
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>

Compare Tools and Replication

Does anyone know of a SQL Compare tool that allows for the disabling of
Replication when doing database compares?
I have a large number of identical merge replicated databases and am using
the Redgate SQL Compare tool. It is a great tool but always identifies every
table as a diff because of the different Replication trigger names. This
kinda defeats the point of using the tool because I still need to examine
every table to ensure consistency. I am looking for a compare tool that will
allow me to disable the comparing of all replication related db objects.
Anything out there?
I have the same issue running merge replication where there are
replication specific objects & a rowguid column in my article tables. I
use DB Ghost from Innovartis to manage my database changes & it ignores
the replication objects during a comparison. To avoid the rowguid
columns coming up as differences, I basically drop replication, drop the
rowguid columns & re-create replication. To do this, I create a file
containing the commands to drop the columns (DB Ghost can generate this
file) & use Enterprise Manager to generate scripts to drop & create my
publications, articles etc. These files are then passed as input to DB
Ghost & executed automatically either before or after the database
comparison.
John McGrath
SQL Server DBA MCSE
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Compare like data

Hello all,

I am new to sql and have some Access experience.

In sql, how do I: compare 2 identical tables, (except for data); then update
table 1 with new data from table 2

TIA

JakeGitarJake (gitarjake@.spammersuntied.com) writes:
> I am new to sql and have some Access experience.
> In sql, how do I: compare 2 identical tables, (except for data); then
> update table 1 with new data from table 2

To find all rows that are different, assuming that the key is keycol:

SELECT *
FROM a
FULL JOIN b ON a.keycol = b.keycol
WHERE a.keycol IS NULL OR
b.keycol IS NULL OR
(a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR
...

For columns that does not permit NULL, you can skip the checks for NULL.

To update:

UPDATE a
SET col1 = b.col1,
col2 = b.col2,
..
FROM a
JOIN b ON a.keycol = b.keycol
WHERE (a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR

DELETE a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE b.keycol = a.keycol)

INSERT a (keycol, col1, col2, ...)
SELECT keycol, col1, col2, ...)
FROM b
WHERE NOT EXISTS (SELECT * FROM a WHERE a.keycol = b.keycol)

You can take some shortcuts here. The simplest way is to say "DELETE a"
and then insert all from b. The long where condition on the UPDATE
statement can be excluded, you only update a few extra rows with the
values they already have.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Compare data in tow users

Does anybody have a script that would be able to compare two users (oracle)
or dB (MSSQL) tables by table, assuming that schema is identical?
Thx
YuriWYou want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
-- Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message =news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users =(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> > Thx
> > YuriW
> >|||I'd rather prefer a sql script.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
You want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
--
Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||Check the following article:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
-Sue
On Wed, 15 Oct 2003 17:13:13 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>I'd rather prefer a sql script.
>"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
>news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
>You want a tool like SQL Data compare:
>http://www.red-gate.com/sql/summary.htm|||in oracle, generate a script from the data dictionary, something like this:
select 'select * from user1.'||table_name|| ' minus select * from user2.' ||
table_name || ';'
from dba_tables where owner = 'USER1';
this show any rows in USER1 that don't exist in USER2 (or have slight
differences)
you could also generate a join or a more complex comparison statement,
depending on what you're looking for
either spool out the output in SQL*Plus, or but this in a PL/SQL block and
use EXECUTE IMMEDIATE to run the generated statements
note that this will not work for tables that have LONG columns
---
Mark C. Stock
www.enquery.com
(888) 512-2048
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||SQL queries are not a terribly good tool for doing this type of comprison.
It would be easier to simply dump out the tables (BCP for MS SQL Server)
into two directories, one for each database. Then, you can compare the
tables -- i.e. their exported files -- using one of many file comparison
tools.
Also, there are commercial tools such as SQL Compare from Red Gate.
I do this myself with Perl scripts. If you are interested, email me and I'll
drop you a copy.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>|||thx, Mark.
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||Hi Mark,
I am alomst there, but just not yet. Can you help me with this sql.
Here are exact steps:
1. set pages 5555 (trying to get all garbidge from temp.sql and it does not
do it?)
2. spool temp.sql
3. select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
where owner = 'MAGNETIC' ORDER BY table_name;
This statement works.
4. spool off
5. @.temp.sql (with an exception some errors like I said in #1).
Now if I run -
select 'select count(*) from magnetic.' || table_name ||';' from dba_tables
where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select count(*)
from
magnetic.' || table_name ||';' from dba_tables where owner = 'MAGNETIC'
ORDER B
Y table_name;
I get - ORA-00936: missing expression
Where is the error in sql?
Thanks in advance.
YuriW
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||"Yuri Weinstein (HotMail)" wrote...
> Now if I run -
> select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
> where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select
count(*)
---^
> from
> magnetic.' || table_name ||';' from dba_tables where owner ='MAGNETIC'
> ORDER B
> Y table_name;
> I get - ORA-00936: missing expression
> Where is the error in sql?
>
Hi Yuri,
it seems to me that you try to construct a string of 2 selects
in a select statement. But if I'm right, you have some problems
with quotes. Your statement is quite ok until you try to put
a concatenation after the ORDER BY.
I believe you want to put the from and order by clauses into
to string.
Try to work from here.
hth,
Guido

compare and update a table from one database to another table on another databas

Hi everybody.. need help on this situation which i am to.

I have two databases named db1 and db2
both of which has two identical tables named tbl1 and tbl2

I need to compare tbl1 of db1 to the tbl2 of db2
if there is a record that is existing on tbl1 and not on the tbl2 then
i need to create a tblnew on db2
from that tblnew then i need to append all the data from tblnew to tbl2 of db2.

I don't know how to start with it because i'm used to appending data on two tables on the same database but not on a different one...

thanks
alexStart here or read BOL about 4 part naming:

http://mssqltips.com/tip.asp?tip=1095|||Are the databases on the same server? Or Different Server or Instance?

In any case, read this

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx|||on the same server

thanks