CODEWITHSUNDEEP
postgresqlsyntaxamazon-redshift
TinaW
TinaW

Reputation: 1017

insert overwrite table in Redshift

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?

Upvotes: 1

Views: 6474

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269400

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:

Upvotes: 5

Related Questions