Currently, I have exported a bunch of SQL Anywhere 12 tables to MS SQL Server - with the help of Breck's great blog article on a script-based transfer. - Thanks again, Breck!
Now I would like to add an automatic test to compare the SA and MSS data - to assure the data transfer has been successful. (Breck's script does check whether all rows are transferred correctly, but I would like to check the contents, too.) - As there are lots of tables, I would prefer a method that can be automated (e.g. by enumerating all tables via a loop over the schema or the like).
But that's more difficult than I'd thought:
Do you have any ideas for further options?
asked 18 May '11, 05:13
In the end, I used a combined approach:
Using a code block to enumerate all relevant tables and build a bunch of SQL statements for each, namely to
Those statements were stored as strings in a temporary table. Finally the result set was OUTPUTed again to build a SQL script. By design, the statements do form a long SQL batch but no code blocks, so OUTPUT can be used within.
Thas script was run with DBISQL (which can OUTPUT proxy tables), and finally I've DIFFed the data files.
Quite slow (particulary compared to the very fast data transfer when exporting the SA data to MSS), but it's automated:)
And now I have noticed that missing the KEEPNULLS option in MS SQL BULK INSERT definetely has introduced some data changes - some rows with NULLs in SA have default values in MS ... - obviously not just relying on "it has the same number of rows in both databases" was a senseful approach ...
answered 18 May '11, 08:41
Maybe this is too simplistic an answer (and the performance is probably not where you need it to be), but suppose your local table is tSA and the corresponding SQL Server table is tMSS, could you not simply compare the value returned by:
select count(*) from tSA
select count(*) from (select * from tSA union select * from tMSS) d
and see if the two counts are the same. Assuming that you have already verified that both tSA and tMSS have the same number of rows, the above union should give you the same count as tSA if both tables are identical.
answered 18 May '11, 08:17