comparing fields data between two tables from two different systems

[Former Member]

Hi ,

Can anyone please tell me "how to compare the SOME fields data between SAP ECC - MARA and SAP MDG-MARA tables" in either SAP Data Service (or) SAP Information Steward tools .

For example , to compare the data for field MARA-BRGEW in both ECC MARA table and MDG MARA table . Here idea is to get know how many records are different between two system tables . Actually data is moving from ECC to MDG on daily basis so business wants to know how many fields per a particular table(MARA or EQUI) are correct between two systems(ECC and MDG)

Please let me know the high level solution .

Thanks

Regards

Mubeen

[former_member316405]

This should work.

SELECT * INTO TAB3 FROM (select * from Tab1 except select * from tab2) AS X;

[former_member187605]

Load both tables in a staging area in a relational database. Then use DS table_comparison transform (check for more details) to compare both tables.

[Former Member]

Hi Dirk

Thanks a lot for your reply .

I want to store the INCORRECT records in an table so that I can create the exception reporting on it.

Can you please let me know COMPLETE  Data Service Data Flow with steps

Thanks

Regards

Mubeen

[Former Member]

Hi Dirk

Thanks a lot for your reply .

I want to store the INCORRECT records in an table so that I can create the exception reporting on it.

Can you please let me know COMPLETE  Data Service Data Flow with steps

Thanks

Regards

Mubeen

[former_member316405]

Hi Abdul,

I would suggest you to store those tables data into some staging tables.Then you can run the below statement in the database to insert the Incorrect records into a new table.

CREATE TABLE A  AS (SELECT * FROM MARA1 MINUS SELECT * FROM MARA2);

Please let me know if this helped you.

Thanks,

Niranjan

[Former Member]

Hi Niranjan,

Is that query works in Sql server?

if not suggest me a query which runs in sql server

Thanks,

Theja.

[former_member316405]

That one works for Oracle database.If you want it for Sql Server ,replace MINUS with EXCEPT keyword.

[Former Member]

Tried replacing minus with Except keyword still it is not executing;

.

[former_member316405]

Can you paste the query you are executing?

[Former Member]

create table tab3 as

(select * from Tab1 except select * from tab2);

error: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'as'

[former_member316405]

This should work.

SELECT * INTO TAB3 FROM (select * from Tab1 except select * from tab2) AS X;

Please let me know if it works for you.

[Former Member]

Yes it worked!!!

Thank you

[former_member316405]

I'm glad it worked for you.Please mark it as correct answer or Helpful answer.

Thank you.