Selecting Data into Compressed Tables

Use select into ... compression to select regular and LOB data directly into a compressed table.

The destination table does not inherit anything from the original table. That is, if the table from which you are selecting data is page-level compressed, the table into which you select the data can be row-level compressed, or not compressed.

You must indicate compression levels if you are selecting large object data into a table.

The behavior of select into on target tables or columns depends on the type of compression you are using.

Compression Type	Behavior of select into on Target Tables	Source Table or Column	Database-wide Setting for compression	Target Table or Column
Data compression	Target table or columns do not inherit any properties from the source table. If you do not specify compression, tables other than temporary tables inherit the database-wide setting for compression. Temporary tables do not inherit any compression settings from the source table, source column, or from the target databases’s attributes.	Table can be compressed or uncompressed, and may include one or more compressed columns.	none	Target table and all columns are uncompressed.
Data compression			row or page	Target table is created with either row or page compression, according to database-wide attribute. All eligible columns are compressed. Note: Index compression compression does not support row compression.
LOB compression	LOB columns in the target table do not inherit any properties from the source columns. If you do not specify compression, LOB columns in target tables other than temporary tables inherit the database-wide setting for the lob_compression attribute. LOB columns in temporary tables inherit nothing from the source table, source column, or from the target database's attributes.	Source LOB columns may be compressed.	lob_compression = 0, unset for the database	All LOB columns in the target table are uncompressed.
LOB compression		Source LOB columns may be compressed.	lob_compression = `compression_level`	All LOB columns in the target table are created using the database-wide setting for lob_compression = `compression_level`.

This example selects all rows from the titles table, and creates a new table named titles_2 with row-level compression:

select * into titles2
with compression = row
from titles

See the Reference Manual: Commands.