"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/
