Pages

Wednesday, April 5, 2023

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.

6 comments:

  1. Very cool. Thank you for posting these articles.

    ReplyDelete
  2. Very slick... I was not aware that except could be used this way...!

    I only ever had used exception on a join statement before...

    This will be very useful...!

    Thanks...

    ReplyDelete
  3. Thanks, great tip!

    ReplyDelete
  4. Thank you Simon
    See you in Denver

    ReplyDelete
  5. Reynaldo Dandreb MedillaApril 13, 2023 at 4:19 AM

    thanks Simon, cool EXCEPT clause, an alternative for sql exception join

    ReplyDelete
  6. Good to know! Thanks for the tip!

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.