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:
DECLARE @sa_t LONG VARCHAR;
DECLARE @mss_t LONG VARCHAR;
UNLOAD TABLE #mss_t
INTO VARIABLE @mss_t;
UNLOAD TABLE #sa_t
INTO VARIABLE @sa_t;
SELECT IF @mss_t = @sa_t
ELSE 'NOT EQUAL'
Here's the output from dbisql:
if @mss_t = @sa_t then'EQUAL' else 'NOT EQUAL' endif
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.