# INSERT

Use INSERT for both single- and multi-row ad hoc inserts. (When inserting many rows, use the more efficient [COPY](https://docs.heavy.ai/v8.3.0/loading-and-exporting-data/command-line/load-data#copy-from) command.)

```
INSERT INTO <table> (column1, ...) VALUES (row_1_value_1, ...), ..., (row_n_value_1, ...);
```

## Examples

```sql
CREATE TABLE ar (ai INT[], af FLOAT[], ad2 DOUBLE[2]); 
INSERT INTO ar VALUES ({1,2,3},{4.0,5.0},{1.2,3.4}); 
INSERT INTO ar VALUES (ARRAY[NULL,2],NULL,NULL); 
INSERT INTO ar VALUES (NULL,{},{2.0,NULL});
-- or a multi-row insert equivalent
INSERT INTO ar VALUES ({1,2,3},{4.0,5.0},{1.2,3.4}), (ARRAY[NULL,2],NULL,NULL), (NULL,{},{2.0,NULL});
```

You can also insert into a table as SELECT, as shown in the following examples:

{% tabs %}
{% tab title="Example 1" %}

```sql
INSERT INTO destination_table SELECT * FROM source_table;
```

{% endtab %}

{% tab title="Example 2" %}

```sql
INSERT INTO destination_table (id, name, age, gender) SELECT * FROM source_table;
```

{% endtab %}

{% tab title="Example 3" %}

```sql
INSERT INTO destination_table (name, gender, age, id) SELECT name, gender, age, id  FROM source_table;
```

{% endtab %}

{% tab title="Example 4" %}

```sql
INSERT INTO votes_summary (vote_id, vote_count) SELECT vote_id, sum(*) FROM votes GROUP_BY vote_id;
```

{% endtab %}
{% endtabs %}

You can insert array literals into array columns. The inserts in the following example each have three array values, and demonstrate how you can:

* Create a table with variable-length and fixed-length array columns.
* Insert `NULL` arrays into these colums.
* Specify and insert array literals using `{...}` or `ARRAY[...]` syntax.
* Insert empty variable-length arrays using`{}` and `ARRAY[]` syntax.
* Insert array values that contain `NULL` elements.

```sql
CREATE TABLE ar (ai INT[], af FLOAT[], ad2 DOUBLE[2]); 
INSERT INTO ar VALUES ({1,2,3},{4.0,5.0},{1.2,3.4}); 
INSERT INTO ar VALUES (ARRAY[NULL,2],NULL,NULL); 
INSERT INTO ar VALUES (NULL,{},{2.0,NULL});
```

## Default Values

If you [create a table](https://docs.heavy.ai/v8.3.0/data-definition-ddl/tables#create-table) with column that has a default value, or [alter a table](https://docs.heavy.ai/v8.3.0/data-definition-ddl/tables#alter-table) to add a column with a default value, using the INSERT command creates a record that includes the default value if it is omitted from the INSERT. For example, assume a table created as follows:

```sql
CREATE TABLE tbl (
   id INTEGER NOT NULL, 
   name TEXT NOT NULL DEFAULT 'John Doe', 
   age SMALLINT NOT NULL);
```

If you omit the name column from an INSERT or INSERT FROM SELECT statement, the missing value for column `name` is set to `'John Doe'`.

`INSERT INTO tbl (id, age) VALUES (1, 36);` creates the record `1|'John Doe'|36` .

`INSERT INTO tbl (id, age) SELECT id, age FROM old_tbl;` also sets all the name values to `John Doe` .