Question How to compare select query results with Text File?

[raysefo]

Hi,

I have a select query result of lets say barcodes. And There is a text file which contains barcodes and some other information line by line. I would like to,
1) compare this query results (barcodes) with the text file and identify that are NOT in the text file
2) compare baarcodes in the text file with the query results and identify that are NOT in the query results

Is there a way to implement this?

Best Regards.

 

[jmcilhinney]

It doesn't really matter where the data comes from. Data is data. You just get the data from the database into a collection, get the data from the file into a collection, then compare the data in the two collections. You can use a couple of loops to perform the comparison or, more succinctly, a LINQ query.

 

[cjard]

I'd upload the text file into the database as a temporary table and then run this:

SELECT bc.barcode, tf.barcode
FROM
  barcodes_in_db bc
  FULL OUTER JOIN
  tmp_from_textfile tf
  ON bc.barcode=tf.barcode
WHERE
  tf.barcode IS NULL OR bc.barcode IS NULL

when bc.barcode is null it means the BC is in the textfile but not the barcodes table and vice versa

 

[cjard]

note if you do it locally, you will only get reasonable performance from using a dictionary (hash table) if your textfile/database table has thousands of records. dont use arrays, lists etc