Search code examples
sqlamazon-redshiftsql-inserttruncate

Redshift If TableA contains data then TRUNCATE TableB and INSERT TableA into TableB ELSE do nothing

I am trying to create a query in Redshift but I am struggling to ether create the correct logic or to get it to run full stop.

I have 2 tables TableA and TableB.

If TableA contains data, then wipe TableB and insert the data from TableA.

Pseudo:

CASE WHEN
(SELECT COUNT(*) FROM TABLEA) > 0
     THEN TRUNCATE TABLEB AND INSERT INTO TABLEB (SELECT * FROM TABLEA)
     ELSE DO NOTHING 
END

Obviously this wont work so do I need to do something with an IF statement?:

IF EXISTS (SELECT * FROM TABLEA)
    BEGIN
        TRUNCATE TABLE TABLEB
        INSERT INTO TABLEB (SELECT * FROM TABLEA)
    END
ELSE
    BEGIN
        PRINT 'nothing in table'
    END
Solution

  • You need to use a stored procedure. Can't nest queries with side effects inside other queries in a normal SQL query.