How to write an query for the following?

Say in a source there are 1 lakh records and in Target there are 1 lakh records. Some records in a column having the mismatch value (.ie., Instead of displaying 1000 in a column, it is displaying as 100 in Target column) The source file is from an DB2 and, Oracle (different source) and, Target is an SQL server. So how to find it by a simple way?

Questions by vignesh.testengg

Showing Answers 1 - 10 of 10 Answers

csg

  • Jan 23rd, 2014
 

Extract the data from the source and the target into excel files and do a lookup or perform an If function ( i.e. compare the 2 sets of data ).....

charu

  • Feb 7th, 2014
 

The best way to ETL large number of records .
Convert the file CVS format and then load it ot the source table.

I m new to this . tell me if i m wrong

  Was this answer useful?  Yes

karimulla

  • Feb 14th, 2014
 

You need to write a Count query on both DBs with where clause mentioning the column = 1000, if there is a mismatch you can confirm, there is some glitch.

  Was this answer useful?  Yes

ravi

  • May 15th, 2014
 

Use minus operator between source and target

  Was this answer useful?  Yes

Grace

  • May 29th, 2014
 

As Karimulla mentioned, you can use the count query as one way of verification:

Select
Column_A
, Count(*)
from Table_A
Group by Column_A
Order by Column_A

Best of luck.

  Was this answer useful?  Yes

chandu

  • Jul 23rd, 2014
 

But the sources are different.How we will apply minus between source and target and one more thing we cant apply count function for 2 different sources.

  Was this answer useful?  Yes

Sathiya seelan

  • Jul 23rd, 2014
 

Read the data from the source and do the lookup on target table and get the column which has different value. Compare it with the current record value. If both are matches, then no issues otherwise load the data into the error table / file.

sagar

  • Aug 11th, 2014
 

Use the linked server feature in MS SQL. Once the linked server is created of the source then you can use the except(minus) feature of the SQL server.

  Was this answer useful?  Yes

mukesh

  • Sep 27th, 2015
 

Considering the source as correct data. Use source data as an external file in oracle and use a MERGE command to Update the date ( Correct the data in the target) or Insert if the entire record is missing. MERGE with External table will be very fast as oracle take care of the parallel load.

  Was this answer useful?  Yes

santhosh gujja

  • Oct 15th, 2015
 

1) Select * from source -- Convert this output to a Excel Sheet
2) Create an empty table with the same table structure as of source and Load the excel sheet into that table in SQL Server.
Then compare the results by using minus query between source and target within SQL Server.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions