Compare Tables With UNLOAD INTO VARIABLE

Q: Let's say you have two tables where you think the data is identical; how do you compare them?

A: UNLOAD the tables and compare the resulting files.

Q: What if one of the tables is a proxy table? UNLOAD doesn't work on proxy tables.

A: Use SELECT * INTO #temp_table, then UNLOAD the temporary table.

Q: What if you want to do everything inside a SQL script, without running a separate "compare the files" step?

A: Use UNLOAD INTO VARIABLE and compare the variables with a SQL statement.

Here's an example showing both techniques, SELECT * INTO #temp_table and UNLOAD INTO VARIABLE, on a pair of proxy tables pointing to Microsoft SQL Server and SQL Anywhere databases:

BEGIN
DECLARE @sa_t  LONG VARCHAR;
DECLARE @mss_t LONG VARCHAR;

SELECT * 
  INTO #mss_t 
  FROM proxy_mss_t;

SELECT * 
  INTO #sa_t  
  FROM proxy_sa_t;

UNLOAD TABLE #mss_t 
  INTO VARIABLE @mss_t;

UNLOAD TABLE #sa_t  
  INTO VARIABLE @sa_t;

SELECT IF @mss_t = @sa_t 
          THEN 'EQUAL' 
          ELSE 'NOT EQUAL' 
       ENDIF;
END;

Here's the output from dbisql:

if @mss_t = @sa_t then'EQUAL' else 'NOT EQUAL' endif
'EQUAL'

If you're working with local tables, you don't need the separate SELECT * INTO #temp_table statements... and maybe someday, when the proxy table feature is enhanced, you'll won't ever need SELECT * INTO #temp_table.

Other possibilities come to mind:

• Using UNLOAD SELECT to reformat row layouts, deal with differing numbers of columns, compare subsets, etc.

• Using a cursor FOR loop to UNLOAD individual rows for comparison.