INSERT
Summary
The INSERT statement adds rows of data to a table.
Syntax
Standard insert with literal values:
INSERT INTO <schema_name.table_name> (col1, col2, ...) VALUES (val_1, val_2, ...) [,(val_1a, val_2a, ...)]; |
Insert using select:
INSERT INTO <schema_name.table_name> (col1, col2, ...) SELECT col_a, col_b, ... FROM <schema_name.table_name>; |
Create table with insert:
INSERT INTO Delta(TableName=‘<schema_name_target.table_name_target>’[, Overwrite=<true>|<false>] [, Unlogged=<true>|<false>])SELECT col_a, col_b, ... FROM <schema_name_source.table_name_source>; |
| Parameter | Description |
|---|---|
TableName |
(Required) Specifies the target schema and table name to insert data into. Connect will create the table if it doesn’t exist, but the given schema must already exist. |
Overwrite |
(Optional) Default is false. Connect will append rows to the target table.
If true, instructs Connect to drop and recreate the target table using the result set. Any data that was in the target table will be lost. Both options rely on the SELECT query to generate the result set. Connect will always create the table if it does not already exist. The table will reflect the column names and data types from the SELECT statement. |
Unlogged |
(Optional) Default is false.
When set to true, unlogged tables may improve the performance of INSERT, UPDATE, and DELETE operations against the target table. Xactly recommends using unlogged tables for transient or temporary data sets only. The risk of data loss in the event of system failure is present when using unlogged tables. |
Details
Connect supports three different styles of INSERT statements. The first two syntax styles should be familiar to users of most relational databases.
The third “INSERT INTO Delta(…)” syntax allows developers to create a new table based on the result set from a SELECT statement. You may also use this syntax to append rows to table that already exists (Overwrite=false).
Examples
/* Insert one row of literal values */
insert into delta.employee_extract
(employee_id, effective_start_date, effective_end_date)
values
('123-34-3333', '2002-07-04', '2008-01-04');
/* Insert five rows of literal values in one statement */
insert into delta.employee_extract
(employee_id, effective_start_date, effective_end_date)
values
('123-34-3333', '2002-07-04', '2008-01-04'),
('123-34-3333', '2008-01-04', '2010-10-31'),
('123-34-3333', '2010-10-31', null),
('abc123', null, null),
('xyz789', '2012-12-04', '2015-01-04');
/* Insert using SELECT */ insert into delta.employee_extract select employee_id, effective_start_date, effective_end_date from xactly.xc_participant;
/* Create an unlogged table, drops and recreates target table */
insert into Delta(TableName='delta.employee_extract', Overwrite=true, Unlogged=true) select employee_id, effective_start_date, effective_end_date from xactly.xc_participant;
