The INSERT Statement

Contact Us or call 1-877-932-8228
The INSERT Statement

The INSERT Statement

A database user with appropriate privileges can use the INSERT statement to add data to a target table. There are a number of variations of the INSERT statement, all of which designate the target table's name. In many cases, the target table's column names are specified as well.

INSERT Statement Categories

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert.sql
INSERT INTO regions VALUES (5, 'Antarctica');

This is an example of a simple single table INSERT statement that does not include column names of the target table. Although this is a convenient shorthand, it is considered a "best practice" for SQL in production settings to explicitly specify the column names. This prevents problems from occuring if table definitions change and makes the intentions of the SQL programmer apparent if the statement requires later support attention.

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert_cols.sql
INSERT INTO regions (region_id, region_name) VALUES (6, 'Australia');

This single table INSERT includes a column name specification.

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert_all_select.sql
INSERT ALL
INTO regions (region_id, region_name) 
		VALUES (reg_id, reg_name)
INTO countries (country_id, country_name, region_id) 
		VALUES (ctry_id, ctry_name, reg_id)
SELECT 
	7 AS reg_id, 
	'Africa' AS reg_name, 
	'ZA' AS ctry_id,
	'South Africa' AS ctry_name 
FROM dual;

This multitable INSERT adds a new country (South Africa) in a new region (Africa) to the database. All of the information that needs to be INSERTed is included in a subquery (that uses the DUAL table). Two different tables (countries and regions) are specified in the statement.

Next