Albert
Thanks - this is very simple, elegant and clever!!
David
----- Original Message -----
From: "Albert Berry" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, December 13, 2002 11:04 AM
Subject: Comparing row counts between 2 dbs
> "David M. Blocker" <[EMAIL PROTECTED]> wrote:
>
> >Al
> >
> >That sounds like a VERY useful routine - would you be willing to share
it?
> >
> >David Blocker
> >
>
> Here you go:
>
> -- ----------------------------------------------------------------------
> -- Compare.cmd
> -- Compares the rows counts in two databases. Used after a reload to
> -- verify that the reload was completed completely.
> -- System tables are included so that the indexes and constraints count
> -- is checked.
> -- ----------------------------------------------------------------------
> SET VAR vdbold = "OLD" -- Source database
> SET VAR vdbNEW = "NEW" -- Reloaded database
> SET VAR MICRORIM_SELMARGIN = 1
> SET LINES 0
> SET HEADINGS OFF
> -- ---------------------------------------------------------------------
-
> -- Export row counts from the original database
> -- ---------------------------------------------------------------------
-
> CONNECT .vdbold
> SET NULL -0-
> OUTPUT TableOLD.DAT
> SELECT sys_table_name, sys_num_rows FROM sys_tables +
> ORDER BY sys_table_name WHERE sys_table_type <> "View"
> OUTPUT SCREEN
> -- ---------------------------------------------------------------------
-
> -- Export row counts from the reloaded database
> -- ---------------------------------------------------------------------
-
> CONNECT .vdbnew
> SET NULL -0-
> OUTPUT TABLENEW.DAT
> SELECT sys_table_name, sys_num_rows FROM sys_tables +
> ORDER BY sys_table_name WHERE sys_table_type <> "View"
> OUTPUT SCREEN
> SET VAR vChkFile = (CHKFILE("compare.rb1"))
> IF vChkFile = 1 THEN
> DELETE compare.rb?
> ENDIF
> -- ---------------------------------------------------------------------
-
> -- Create a database to compare the row counts
> -- ---------------------------------------------------------------------
-
> CREATE SCHEMA AUTHORIZATION compare PUBLIC
> CREATE TABLE TableOld (TableName TEXT (18), TableRows INTEGER)
> CREATE TABLE TableNew (TableName TEXT (18), TableRows INTEGER)
> CREATE TABLE TableCompare (TableName TEXT (18), NewCount
INTEGER,OldCount INTEGER)
> SET NULL -0-
> -- ---------------------------------------------------------------------
-
> -- Load the data. Load and update a table to compare the row counts
> -- ---------------------------------------------------------------------
-
> LOAD TableNew FROM TABLEnew.DAT +
> AS FORMATTED USING TableName 1 18,TableRows 21 29
> LOAD TableOld FROM TABLEold.DAT +
> AS FORMATTED USING TableName 1 18,TableRows 21 29
> INSERT +
> INTO TableCompare (TableName, OldCount) SELECT TableName,TableRows +
> FROM TableOld
> UPDATE TableCompare SET NewCount = t2.TableRows +
> FROM TableCompare t1,tableNew t2 WHERE t1.TableName = t2.TableName
> -- ---------------------------------------------------------------------
-
> -- If there is a difference in the row counts, not all of the data made
it
> -- If the new count is null, the whole table failed to make it
> -- ---------------------------------------------------------------------
-
> SELECT COUNT(*) INTO vCount FROM TableCompare WHERE OldCount <> NewCount
OR NewCount IS NULL
> IF vCount = 0 THEN
> PAUSE 1 USING = "No errors found" AT center center DEFAULT white ON
green
> ELSE
> PAUSE 1 USING "Errors found" AT center center DEFAULT white ON red
> BROWSE * FROM TableCompare WHERE oldcount <> newcount OR newcount IS
NULL
> ENDIF
> RETURN
>
>
> __________________________________________________________________
> The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp
>
> Get your own FREE, personal Netscape Mail account today at
http://webmail.netscape.com/
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
