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:

?

1

<br>BEGIN<br>DECLARE @sa_t  LONG VARCHAR;<br>DECLARE @mss_t LONG VARCHAR;<br><br>SELECT * <br>  INTO #mss_t <br>  FROM proxy_mss_t;<br><br>SELECT * <br>  INTO #sa_t  <br>  FROM proxy_sa_t;<br><br>UNLOAD TABLE #mss_t <br>  INTO VARIABLE @mss_t;<br><br>UNLOAD TABLE #sa_t  <br>  INTO VARIABLE @sa_t;<br><br>SELECT IF @mss_t = @sa_t <br>          THEN 'EQUAL' <br>          ELSE 'NOT EQUAL' <br>       ENDIF;<br>END;<br>

Here's the output from dbisql:

?

1	<br>if @mss_t = @sa_t then'EQUAL' else 'NOT EQUAL' endif<br>'EQUAL'<br>

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.