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