SQL Insert Into

In contrast to the SELECT statement in SQL which returns data from a table (or tables), the INSERT statement adds records to a table. This new data can either be specified as part of the SELECT statement itself, or be records returned from another query by combining the INSERT statement with a SELECT statement.

In the former case, the INSERT statement in SQL takes the form as below, including VALUES and a comma-delimited list of the data to insert for each row, enclosed in parentheses:

INSERT INTO <table> VALUES (a,b,c), (d,e,f), (h,i,j), …

This is becoming a bit complex, so perhaps it is best to look again at an example with our employees table. Assuming we have the correct permissions to write data to the table, if we wanted to add two new employees, John Doe and Sally Forth, using an INSERT statement, we could do so with the statement below:

INSERT INTO employees VALUES (100, '1954-01-02', 'John', 'Doe', 'M', '1984-01-01'), (101, '1963-03-17', 'Sally', 'Forth', 'F', '1989-12-12');

After which we can check our result with a simple query using a SELECT statement:

SELECT * FROM employees;

While using INSERT INTO with VALUES can be useful for the ad hoc addition of records, this is uncommonly done by an analyst directly, and more likely to appear in automatically generated scripts for the bulk insertion or loading of data.

In the next section we address the combination of using the INSERT statement in conjunction with a SELECT statement which would be more commonly done by a practicing Data Analyst or Data Scientist.