2 Databases compare

[#1]

Hi

I have two Databases. They have both the same tables with the same name. I have to check if there are in both the same data. How should I do that?

[Andrea_Ghirardello]

You have several options:

A- you can create a table using inner join

B- you can load at first one table (master one) adding a new field (flag_1=1), then load the second one using different alias with an additional field (flag_2=2), finally you can create a  table containing rows from both tables in this way:

1. Create new table FINAL_TABLE adding rows from table_1 inner join table_2 with a new field (flag=3)

2. add rows from table_1 where keys not exists in FINAL_TABLE (or using left join) and set flag=1

3. add rows from table_2 where keys not exists in FINAL_TABLE (or using right join) and set flag=2

4. drop tables table_1 and table_2

solution B is more complicated but you can identify each row: Rows with flag = 3 are rows that are common to Table_1 and Table_2; rows with flag = 1 are rows that are present only in Table_1; rows with flag = 2 are rows that are present only in Table_2

[#3]

How can I add a new field?

[Andrea_Ghirardello]

For example:

LOAD

     Field1,

     Field2,

     1 as AdditionalField

FROM .\YourData.qvd (qvd);

[Andrea_Ghirardello]

See attached example

[#6]

Hi

It's near to work but if i load it then i got an error: Table not found.

Isn't it possible to get a name to an ODBC Connection or do i have to take the name like in SQL: FROM a.table?

[Andrea_Ghirardello]

Do you mean that the example raise an error while reloading?

Did you estract source data .xlsx from the zip?

[#8]

No yours working fine. I did one for my two ODBC Databases and there it didnt work

[Andrea_Ghirardello]

Post the script or a test environment.

[#10]

ODBC CONNECT TO [DB01;DBQ=DB01] (XUserId is asdasdasd, XPassword is aPassword);

"DB01":

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

                   DATETIME    

FROM Test1.table1;

ODBC CONNECT TO [DB02;DBQ=DB02] (XUserId is asdasdasdasdasssda, XPassword is anotherPassword);

"DB02":

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

                     DATETIME

FROM Test2."table2";

"InnerJoinTable":

Load *

Resident DB01;

Inner Join load *

Resident DB02;

"UnionTable":

Load *,

  'matching rows' as Flag

Resident InnerJoinTable;

add Load *,

  'DB01' as Flag

Resident DB01;

add LOAD *,

  'DB02' as Flag

Resident DB02;

drop Table InnerJoinTable;