CODEWITHSUNDEEP
sqldatabaseamazon-redshift
Aakash Basu
Aakash Basu

Reputation: 1767

SQL Insert statement with inner query not working

Continuation of AWS Redshift Table Delta Load Plan's Guidance

The select given in the below query works fine and returns the new inserts in the stage table:

SELECT A, B, C, D, E, F, G
      FROM TABLE_STAGE A
     WHERE NOT EXISTS (SELECT B.ID
                     FROM TABLE_FINAL B
                    WHERE B.B = A.B
                      AND B.C = A.C
                      AND B.D = A.D
                      AND B.E = A.E);

but when I am trying to insert those into the final table using the below statement, it fails:

INSERT INTO TABLE_FINAL values
(SELECT A, B, C, D, E, F, G
  FROM TABLE_STAGE A
 WHERE NOT EXISTS (SELECT B.ID
                 FROM TABLE_FINAL B
                WHERE B.B = A.B
                  AND B.C = A.C
                  AND B.D = A.D
                  AND B.E = A.E));

Is the query right, for getting only those rows where the composite key is new and doesn't exist in the final table. What is the most efficient way of doing that, if this is not the right solution. How to do it?

Upvotes: 0

Views: 413

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

When doing an insert, you should always include the list of columns:

INSERT INTO TABLE_FINAL (A, B, C, D, E, F, G)
    SELECT A, B, C, D, E, F, G
    FROM TABLE_STAGE A
    WHERE NOT EXISTS (SELECT B.ID
                      FROM TABLE_FINAL B
                      WHERE B.B = A.B AND
                            B.C = A.C AND
                            B.D = A.D AND
                            B.E = A.E
                     );

If this fails, then the most common reason would be inconsistent types in the columns. It is also possibly that you are not assigning a value to a column declared as not null that has no default value.

Upvotes: 2

hotfix
hotfix

Reputation: 3396

try this one:

INSERT INTO TABLE_FINAL 
SELECT A, B, C, D, E, F, G
  FROM TABLE_STAGE A
 WHERE NOT EXISTS (SELECT B.ID
                 FROM TABLE_FINAL B
                WHERE B.B = A.B
                  AND B.C = A.C
                  AND B.D = A.D
                  AND B.E = A.E);

Upvotes: 1

Related Questions