insert overwrite table in Redshift

Question

I want to fully overwrite a Redshift table.

In e.g. Hive, I could do the following:

INSERT OVERWRITE INTO target 
SELECT s.* FROM staging s LEFT JOIN target t
ON s.primaryKey = t.primaryKey AND s.distKey = t.distKey
WHERE t.primaryKey IS NULL;

I don't get this to work in Redshift (other than dropping and re-creating the table).

Does anybody know what the Redshift syntax is for overwrite?

Answer 1

While there is no command equivalent to INSERT OVERWRITE, you can do this via:

• TRUNCATE <table>
• Use SELECT INTO

eg: SELECT <column> INTO <table> FROM <etc>

The TRUNCATE command is a very efficient way to delete all contents of a table. However, it cannot be reversed by a ROLLBACK, so make sure you really want to do it!

See:

• TRUNCATE documentation
• SELECT INTO documentation