In Redshift, you can move data from a source table to a target table by using ‘ALTER TABLE APPEND’.
Syntax:
ALTER TABLE target_table APPEND FROM source_table;
You can also perform this task in 2 steps :
step1: Insert into target_table (select * from source); step2: Truncate source table
‘ALTER TABLE APPEND’ moves data blocks; whereas Inserting and truncating does duplicate the data and then deletes it. Hence, ‘ALTER TABLE APPEND’ option is more efficient.
To Append the data from one table to another, both source table and target table should have columns with same name and attributes.
The below column attributes should match on both source table columns and target table columns.
- Column Name
- Column Size
- Data type
- Not Null
- Sort style
- Distribution style
- Sort key columns
- Distribution key columns
If source table has additional columns that are not in the target table, you can use ‘IGNOREEXTRA’ option.
ALTER TABLE target_table APPEND FROM source_table IGNOREEXTRA;
If target table has additional columns that are not in the source table, you can use ‘FILLTARGET’ option to populate those additional columns with either default value or null value.
ALTER TABLE target_table APPEND FROM source_table FILLTARGET;
You cannot use both ‘IGNOREEXTRA’ and ‘FILLTARGET’ options together in the same statement.
Identity columns cannot be appended using ‘ALTER TABLE APPEND’. If source table has an identity column, use IGNOREEXTRA option to ignore it. If target table has an identity column, use FILLTARGET option to auto populate it. If both source table and target tables have identity columns, you cannot append data using ‘ALTER TABLE APPEND’.