Wow, Emmitt - you just found a great enhancement! One of these years, you should code the changes and send them up.
My NULL setting is different, and my R:Style settings vary from yours, but here it is:
--- ----------------------------------------------------------------------
--- 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.
---
--- original by Albert Berry, adapted by Emmitt Dove 12/13/02
--- ----------------------------------------------------------------------
DISCONNECT
SET STATICDB ON
SET VAR vdbold = 'OLDdbname' -- Source database
SET VAR vdbnew = 'NEWdbname' -- Reloaded database
SET VAR MICRORIM_SELMARGIN = 1
SET LINES 0
SET HEADINGS OFF
-- ----------------------------------------------------------------------
-- Export row counts from the original database
-- ----------------------------------------------------------------------
CONNECT .vdbold
SET NULL -
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 -
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 -
-- ----------------------------------------------------------------------
-- 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
DELETE tableold.dat
DELETE tablenew.dat
RETURN
Manager, DairyPak Business Systems
Blue Ridge Paper Products, Inc.
40 Lindeman Drive
Trumbull, CT 06611
(203) 673-2231
[EMAIL PROTECTED]
[EMAIL PROTECTED]
