Version 3 of the Foxhound Database Monitor is now available...

Wednesday, September 16, 2009

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.

2 comments:

Anonymous said...

Breck,

thanks for posting again - after such a quiet August...

And the comparison with UNLOAD ... INTO VARIABLE is a great idea I will consider in the next database upgrade/migration process. So far I've typically used UNLOAD/OUTPUT to file and then used WinDiff to compare.

Q: How can I compare SQL Anywhere to MS SQL Server?
A: (Is left as an exercise to the reader - Hint: "EQUAL" is not correct.)

Regards
Volker

tom s. said...

Yay, Breck's back!