Question:
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.
Answer:
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:
- Create a “staging table” that would be all SQLCHARs.
- Name the column names as mirrors of the target table.
- Use DTS to import the text file into the staging table.
- 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.
