Data compare between two databases using Data Point

Toad Data Point
1

Hi,

Will Toad for Data Point accommodate to compare between two database with translation in column values? And please provide the steps.

Thanks,

Vijay

2

I'm not sure what do you mean by "translation in column values". Toad data point can perform data compare between different databases such as Oracle to MySQL by converting those database values to built-in .NET values. The only exception is if your particular database's data type has a higher range or precision, the data compare may fail.

Here's the supported data type range and precision limitations:

  • Decimal (numeric) data: 28-29 significant digits
  • Date and time values: from 00:00:00 January 1, 0001 A.D. to 23:59:59 December 31, 9999 A.D., precision 10-7 seconds
  • Time intervals: from +107 to -107 days, precision 10-7 seconds
  • Floating point numbers: from ±5.0 × 10−324 to ±1.7 × 10308, 15-16 significant digits
  • Text data: up to 2 Gigabytes
  • Binary data: up to 2 Gigabytes
    Please refer to this demo on Cross-Platform Compare and Synch http://www.toadworld.com/products/toad-data-point/m/media-library/1165.aspx

And here's the supported database platforms

DataCompareSupport.png
DataCompareSupport.png682×580 5.7 KB

3

Hi,

Let me phrase my question with an example.

  • column A in table 1 (source) has a value of 123

  • column B in table 2 (target) has a value of ABC

As per the source to target mapping, when we compare the two tables, result should be passed.

Will the tool has a capability of comparing the transformed or translated values between tables?

4

Toad only does data compare on compatible data types and does not do any mapping or transformation. To achieve the goal in your example, you need to do data transformation first to replace 123 with ABC in table 1 and put the result into another table, say table 3, then you can compare table 2 with table 3 .

Please check out the data tranformation and cleanse feature in our latest 3.7 release.

www.toadworld.com/.../toad-data-point-transformation-and-cleanse-part-1.aspx

5

Thanks!

A follow up question,

Say table 1 and table 2 data of source are combined and reflected in table 3 of target.

Does the tool be able to validate the above scenario?

6

Could you give me an example? Do you mean 3-way compare?

7

For example,

Source - Table A has 10 columns and table B has 10 columns.

Target - Table C has columns from Table A and table B.

Will the tool be able to compare the data between source and target for the above example?

8

Hi vijaymecad,

I think that Compare is able to do it. Below is a picture for your question where you can see how it is working.

I hope it helped you. If you have any other question please let me know.

Filip

9

Thanks!

Also, is there a way to compare “Date” Columns between Oracle and SQL Server. Because, in SQL server, it contains milliseconds as well, but in Oracle it doesn’t.

For Example,

In Source SQL Server - 2015-05-16 12:40:20.923

In Target Oracle Database - 2015-05-16 12:40:20

10

I tried it and seems the data compare igores milliseconds. We will investigate and let you know why.

Thanks!

11

How to compare

12

Hi,

you can run a comparison between Oracle date columns and SQL Server but when one of the datatypes contains miliseconds and the other does not it then lists it as different. Thanks

13

Hi,

That’s exactly the question.

Is there a way to truncate milliseconds in Oracle database and then comparison can take place?

Thanks!

14

There is no special setting in Compare for formatting Date but there are workarounds:

  1. You can create a special View and prepare your data here for comparing. Then you can use this View instead of original Table for Compare.

  2. There is also an option to compare data directly from Editor result. So you can create Select statement with data and compare it with result from other Editor.

I hope this helps.

Filip

15

If the SQL server has blank value in the columns and Oracle reelects the columns as “Null”, and hence the tool shows these columns as mismatch. But its actually not a mismatch, how to come over the issue?

16

You could cretae SQl Server View and use NULLIF(col_with_empty_values, ‘’) function.

https://technet.microsoft.com/en-us/library/ms177562.aspx

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

17

Can I automate the export to the database …??

18

Hi,

what do you mean? Could you describe your question more detail?

Regards

Lyla Su