Merging two files

[taherte001]

I need to merge the archive version of a transaction file and the production version of the same file without including duplicate keys. I also need to filter the records to a range of transaction dates and specific transaction codes: i.e. tsdate between 1150901 and 1160831 and tscode in ('03', '09', '13', '23', '30', '18', '21', '25', '40'). The archive file is currently 11,000,000 records (and growing), of which 200,000 meet the filter criteria. The production version is 1,000,000 records, of which 500,000 meet the filter criteria.

The key to the files is company (tsco), part number (tcpn), transaction code (tscode), transaction date (tsdate), transaction time (tstime).

The results of the merge will be used in an SQL statement that has four CTE's, so the merge can be one of the CTE's or can be created as a separate routine within the program.

I want to get this completed over the weekend, and since the weekend is nearly upon us, I'm hoping to get some suggestions/examples as I dig into writing it from scratch.

[taherte001]

Looking through old posts and have found some possibilities but also found two complicating factors that I wasn't aware of:
1. The archive file has two more fields than the production version (archive date, archive user). I don't care about these extra fields.
2. The production file has 71 columns. The archive file has 73.

Therefore, techniques that involve having to specify individual fields are not practical.

[tomliotta]

...techniques that involve having to specify individual fields are not practical.

Two questions: First, why is it "not practical"? I can understand that it's more work, but it is practical. Lists of column names can always be created in text and copy/pasted as often as needed. And second, what is your question? What problem are you trying to solve? Are you only wanting to reduce the amount of typing or of copy/pastes? Or do you need to know how a merge can be done?

[taherte001]

Perhaps "not practical" wasn't the best term - I didn't want to deal with 71 fields in multiple places within a MERGE statement, if that was what people would recommend. I decided to create my work file using only the fields that would be needed in the CTE in the next steps using a three step process:

1. CREATE TABLE in qtemp.
2. Use an INSERT to copy records from the file in the archive library that match the filter criteria.
3. Use MERGE to copy records from the file in production that match the filter criteria and don't duplicate records in the qtemp file.

I haven't tested it yet but I suspect that the performance of the CTE will be better if I create an index on the qtemp table. I've never done that before but it looks straightforward. QUESTION: is it best to create the index after the table is created in step 1 or after the table has been populated in steps 2 and 3?