INSERT INTO

The INSERT INTO statement inserts new rows, with values, into a table.   It is useful for populating tables and for combining multiple tables into one table.

 

INSERT INTO <table> <table-org>

 

There are two ways to use INSERT INTO.  In both cases the first argument is <table>, the name of the table into which rows will be inserted.   After that we can either specify the values for however many rows we will add, or we can specify the column names to be filled and then the corresponding values for each column.  The latter approach allows us to populate only some columns for each row while leaving the other columns with NULL values or, in the case of the special mfd_id field, being auto-populated.

 

An example of the first form:

 

CREATE TABLE t (a INT32, b NVARCHAR);

INSERT INTO t VALUES (1, 'abc'), (2, 'ijk'), (3, 'xyz');

 

Creates a table:

 

 

Since we did not specify the column names to be filled, the list of values (which is a query, since the VALUES statement returns a table) must have the same number of fields as the table.

 

An example of the second form, specifying the columns to be filled and then the corresponding values for each column:

 

CREATE TABLE t (a INT32, b NVARCHAR, c NVARCHAR);

INSERT INTO t (a, c) VALUES (1, 'abc'), (2, 'ijk'), (3, 'xyz');

 

Creates a table:

 

 

We specified that the a column and the c column should be filled, so the two values for each row that are provided in the VALUES statement are used with the first value going into the a column and the second value going into the c column.

 

 

CREATE TABLE t2 (

  mfd_id INT64,

  INDEX mfd_id_x BTREE (mfd_id),

  a INT32);

 

INSERT INTO t2 (a)

  TABLE CALL ValueSequence(0, 1000, 3);

 

Creates a table:

 

 

When populating a table that already has an automatically populated mfd_id field in it, we will want to specify the destination column as something other than the mfd_id field, so that field can be automatically populated.

 

We can take the values from a SELECT query.    

 

 

Suppose we have a table with four fields, an mfd_id field and three text fields.   We can take records from that table and insert them into a new table that we create.   We create a table with an mfd_id identity field and an index, with three additional text fields, and then we populate that table using INSERT INTO with a selection of records from the recipes table:

 

CREATE TABLE dinners (

  mfd_id INT64,

  INDEX mfd_id_x BTREE (mfd_id),

  a NVARCHAR,

  b NVARCHAR,

  c NVARCHAR);

 

INSERT INTO dinners (a, b)

  SELECT sauce, pasta FROM recipes

    WHERE base <> 'meat';

 

Creates a table:

 

 

The mfd_id field is automatically populated by the system as records are added.   The values for the records go into the a and b columns specified, with the values taken from the corresponding fields, sauce and pasta, that were selected from the recipes table.

 

We can populate fields in whatever order we want:

 

CREATE TABLE dinners2 (

  mfd_id INT64,

  INDEX mfd_id_x BTREE (mfd_id),

  a NVARCHAR,

  b NVARCHAR,

  c NVARCHAR);

 

INSERT INTO dinners2 (a, c, b)

  SELECT base, sauce, pasta FROM recipes

    WHERE base <> 'meat';

 

Creates a table:

 

 

In the prior examples, we have created a table and then we populated it with INSERT INTO.    We can also use INSERT INTO to add records to an existing table, such as the dinners2 table just created above:

 

INSERT INTO dinners2 (a, c, b)

  SELECT base, sauce, pasta FROM recipes

    WHERE base = 'meat';

 

Adds records to the existing table:

 

Combining Tables

INSERT INTO is a handy way to combine multiple tables into a single table.    

 

Suppose we have downloaded geocoded tables in CSV format providing the locations of all fire stations in California, but the data is grouped into a separate CSV file for each county in California.  All the tables have the same schema, that is, the same arrangement of fields with the same field names and data types.   We can import all of the CSV files in one Import session to create a table for each county.

 

We would like to combine all of the tables into a single table.  Suppose the individual county tables are named with the name of the county, with table names such s Alameda, Alpine, Amador and so on.   We can create a single table with data from all of the individual county tables using INSERT INTO as follows:

 

SELECT * INTO [All Counties] FROM [Alameda];

 

INSERT INTO [All Counties] SELECT * FROM [Alpine];

INSERT INTO [All Counties] SELECT * FROM [Amador];

INSERT INTO [All Counties] SELECT * FROM [Butte];

INSERT INTO [All Counties] SELECT * FROM [Calaveras];

INSERT INTO [All Counties] SELECT * FROM [Colusa];

 

... and so on.

 

The SELECT ... INTO statement creates a new table called All Counties by copying all data from the Alameda county table.   Each subsequent INSERT INTO statement adds another county table's records into the All Counties table.   

 

Another way to combine many tables into one is a sequence of nested UNION or UNION ALL statements, but INSERT INTO is quicker and simpler in many cases.

 

Notes

Guanciale is meat - Amatriciana sauce is made with tomatoes, Pecorino Romano cheese, black pepper and guanciale, a cured pork product with a thick strip of fat, for which pancetta or bacon are often substituted if guanciale is not available.  It definitely counts as "meat" if we divide our sauces into meat based or not meat based.   

 

Guanciale is also the base for spaghetti alla carbonara.   Pancetta can be used in a pinch, but guanciale is best.  There is no cream used in classic spaghetti carbonara: the creaminess comes from the emulsion that is formed from the rendered pork fat well mixed with egg and a splash of cooking water from the spaghetti.  In Italy, ground black pepper is liberally applied, for a zesty carbonara.

 

See Also

Queries

 

Command Window

 

Query Builder

 

SQL Statements

 

TABLE

 

UNION / EXCEPT / INTERSECT

 

VALUES