Other Tables

An SQL INSERT statement adds one or more records to any single table in a relational database. more...

Home
Bath
Bedding
Furniture
Bedroom Furniture
Children's Furniture
Dining Room Furniture
Kitchen Furniture
Living Room, General...
Bean Bags, Inflatables
Benches
Bookcases
CD, Video Racks
Chairs
Armchairs, Club Chairs
Barcelona Chairs
Folding Chairs
Lounge Chairs
Other Chairs
Recliners
Rocking Chairs, Gliders
Theater Seating
Chests, Cabinets
Entertainment Ctrs, TV...
Futons, Futon Covers
Futon Covers
Futon Frames
Futons
Other Futons, Futon Covers
Occasional Tables
Card & Game Tables
Coffee Tables
Console, Sofa Tables
End Tables
Other Tables
Table Sets
Tray Tables
Other Furniture
Ottomans, Footstools
Sofas, Loveseats
Loveseats
Other
Sectionals
Sets
Sofa Beds, Sleeper Sofas
Sofas
Trunks
Office
Gardening & Plants
Home Decor
Lamps, Lighting, Ceiling...
Patio & Grilling
Pools & Spas
Rugs & Carpets

Basic form

Insert statements have the following form:

INSERT INTO table (column1, ) VALUES (value1, );

The number of columns and values must be the same. If a column is not specified, the default value for the column is used. The values specified (or implied) by the INSERT statement must satisfy all the applicable constraints (such as primary keys, CHECK constraints, and NOT NULL constraints). If a syntax error occurs or if any constraints are violated, the new row is not added to the table and an error returned instead.

Example:

When values for all columns in the table are specified, then a shorthand may be used, taking advantage of the order of the columns when the table was created:

INSERT INTO table VALUES (value1, );

Example (assuming that 'name' and 'number' are the only columns in the 'phone_book' table):

Advanced forms

Multirow inserts

An SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement:

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2.

Example (assuming that 'name' and 'number' are the only columns in the 'phone_book' table):

which may be seen as a shorthand for the two statements

Note that the two separate statements may have different semantics (especially with respect to statement triggers) and may not provide the same performance as a single multi-row insert.

To insert multiple rows in MS SQL you can use such a construction:

Note that this is not a valid SQL statement according to the SQL standard (SQL:2003) due to the incomplete subselect clause.

To do the same in Oracle use the DUAL table, which always consists of a single row only:

A standard-conforming implementation of this logic shows the following example, or as shown above:

Copying rows from other tables

An INSERT statement can also be used to retrieve data from other, modify it if necessary and insert it directly into the table. All this is done in a single SQL statement that does not involve any intermediary processing in the client application. A subselect is used instead of the VALUES clause. The subselect can contain joins, function calls, and it can even query the same table into which the data is inserted. Logically, the select is evaluated before the actual insert operation is started. An example is given below.

Read more at Wikipedia.org