Easy way in SQL to insert records from one file that are not in the other

In this scenario there are two files with identical field names, and they have the same data types too. I was asked if there is an easy way, using SQL, to insert all records from one file into a second file, omitting records that match ones that are in the second file.

I wanted to come up a solution where I did not have to give any field/column names in the statement. For all I knew the files in questions had many, many field names.

I created a file, I called FILE1, with four fields. Then I used the Create Duplicate Object command, CRTDUPOBJ, to create a duplicate, which I called FILE2.

FILE1 contained four records, which I can show using the following SQL statement:

01 SELECT * FROM FILE1

The results are:

FLD1 FLD2 FLD3 FLD4 ---- ---- ---- ---- a a a 1 b b b 1 c c c 1 d d d 1

FILE2 only contained two records, which can be shown using:

01 SELECT * FROM FILE2

These two records are duplicates of records in FILE1:

FLD1 FLD2 FLD3 FLD4 ---- ---- ---- ---- d d d 1 a a a 1

I need to be able to identify which records in FILE1 are not in FILE2. Fortunately this is easy when I use EXCEPT clause. The show which records are in FILE1 and not in FILE2 my statement would be:

01 SELECT * FROM FILE1 02 EXCEPT 03 SELECT * FROM FILE2

As FILE1 come first, line 1, those records that are in FILE1 and not in FILE2 will be returned. By using the asterisk ( * ) I am comparing all the fields in both files to one another.

The results are:

FLD1 FLD2 FLD3 FLD4 ---- ---- ---- ---- c c c 1 b b b 1

Which are the two records not in FILE2.

If I reverse the order of the files no results are returned, as all of FILE2 records are matched to records in FILE1.

The final step is to convert the above into an Insert statement. This would be:

01 INSERT INTO FILE2 02 SELECT * FROM FILE1 03 EXCEPT 04 SELECT * FROM FILE2

Line 1: This is the only line I had to add to convert the previous statement into one what would output to FILE2.

The results are:

FLD1 FLD2 FLD3 FLD4 ---- ---- ---- ---- c c c 1 b b b 1 d d d 1 a a a 1

By all means the order of the records is different from FILE1, but all the records from FILE1 are there in FILE2.

I like this method as I have not had to enter any field names in the above statements. Thus, this logic works for a file with just four fields, and for one with a hundred. A simple statement that fits the scenario for the person who asked the original question well.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.