INSERT QUERY IN SQL

The 'INSERT INTO' SQL query makes inserting new data into the database very easy. All you need to do is specify into which table you want to insert data, into which columns to insert data, and finally what data to be inserted.

The basic syntax is as follows:

INSERT INTO table_name (column_names) VALUES (data_values)

In general, a SQL-based DBMS provides these ways to add new rows of data to a database:

• Single-row INSERT: A single-row INSERT statement adds a single new row of data 
to a table. It is commonly used in daily applications—for example, data entry programs.

• Multi-row INSERT: A multi-row INSERT statement extracts rows of data from another part of the database and adds them to a table. It is commonly used, for example, in end-of-month processing when old rows of a table are moved to an inactive table, or when monthly results are summarized into a table that has been set up to hold them.

Single-Row Inserts:

Example:

The following example adds a county to the gov_unit table. The values in the VALUES clause correspond to the columns listed after the table name:

INSERT INTO gov_unit (id, parent_id, name, type) VALUES (13, 3, 'Chippewa', ‘County');

Example 2:

The following examples insert data into same columns as above query but prompts user to insert values for columns and can be run again and again to insert set of values:

INSERT INTO gov_unit (id, parent_id, name, type) VALUES (&id, &parent_id, &name, &type);

Any columns you omit from an INSERT statement take on their default values specified at table-creation time. If you do not specify a default value at table-creation, then a null is used.

Inserting NULL/DEFAULT values:

Use the null keyword to insert a null value explicitly in a column that might otherwise default to a non-null value. Use the DEFAULT keyword to specify explicitly that a column should take on its default value.

Example:

INSERT INTO gov_unit (id, parent_id, name, type) VALUES (14, DEFAULT, ‘Mackinac', NULL);

Inserting All Columns:

If your VALUES list contains a value for each of the table’s columns in the order specified at table creation, you can omit the column list.

Example:

INSERT INTO gov_unit VALUES (15, DEFAULT, 'Luce', 'County');

For anything other than an ad-hoc insert (in other words, for inserts you embed in your scripts and programs), it’s safer to specify a list of columns. Otherwise, such queries can fail the moment a new column is added to the target table.

Multirow/ Subquery Inserts:

The second form of the INSERT statement, adds multiple rows of data to its target table. In this form of the INSERT statement, the data values for the new rows are not explicitly specified within the statement text. Instead, the source of new rows is a database query, specified in the SELECT statement.

Example:

For example, suppose you want to copy the order number, date, and amount of all orders placed before January 1, 2008, from the ORDERS table into another table, called OLDORDERS. The multi-row INSERT statement provides a concise, efficient way to copy the data:

INSERT INTO oldorders (order_num, order_date, amount) SELECT order_num, order_date, amount FROM orders WHERE order_date < '2008-01-01';

Insert Targets:

All platforms allow inserts into a view. Oracle also allow inserts into subqueries (or online views):

Example:

INSERT INTO (SELECT id, name, type FROM gov_unit)  (id, name, type) VALUES (19, 'Keweenaw', 'County');