Comparing Text File with Existing Table

Comparing Text File with Existing Table

How can I go about reading in a (comma-delimited) text file and comparing it to an existing table? If there is a match on the key field, the row should be updated. If there is no match on the key field, then the row should be added.

I am using SQL 7.0. The front end is a Web browser, either Microsoft Internet Explorer or Netscape Navigator.

You are fortunate to be undertaking this in SQL 7.0, because you get a very capable tool to manage the conversion, namely Data Transformation Services (DTS).

DTS is like BCP on steroids, not from a speed perspective, but from a usability perspective. In DTS, you define a job that consists of steps. Each step has a successor based on the success or failure of its predecessor. These jobs are saved and can be kicked off as scheduled tasks or run whenever you want.

Inside DTS, I would do the following:

  1. Create a “staging table” that would be all SQLCHARs.
  2. Name the column names as mirrors of the target table.
  3. Use DTS to import the text file into the staging table.
  4. Either convert on-the-fly or use CONVERT to massage the SQLCHAR fields so you can INSERT from the staging table into the Target table.

Either do this yourself in small steps by making these SQL tasks or take advantage of a DTS feature called Data Driven Query (DDQ).

Once you have the cleaned-up data in a proper table, use DDQ to search a lookup table and check whether the value of the column in the current row exists in the lookup table. If the row exists in the lookup table, update the row; if the current row does not exist in the lookup table, insert it. This is specified in the VBScript page of the DDQ.

Share the Post:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved