SQL - Insert

The insert clause has one function; to insert data into a table. Insert populates each table column with a value. Rows are inserted one right after another into the corresponding column.

INSERT INTO employees (Lastname,Firstname,Title)
 VALUES(Johnson,David,crew);

Above we have a single line example of the insert syntax. Since our 'id' column is set up to auto increment, we can omit this field from our script. Our SQL program automatically begins counting starting with one, when the auto increment attribute is added to an integer field.

Display:

SQL - Insert defaults and nulls

We mentioned setting up default or null values for table columns. Simply placing the word default or null, in place of a value is the solution.

INSERT INTO employees (Lastname,Firstname,Title)
 VALUES('Hively','Jessica',DEFAULT);
or
INSERT INTO employees (Lastname,Firstname,Title)
 VALUES('Hively','Jessica',NULL);

SQL - Inserting multilpe values

Here's an example of how to insert more than one record at a time. Many web developers will use the single example above along with HTML forms to continually insert and update their SQL tables.

INSERT INTO employees VALUES
(DEFAULT,'Hicks','Freddy','crew'),
(DEFAULT,'Harris','Joel','crew'),
(DEFAULT,'Davis','Julie','manager');

We use a default value for the id field so that it will continue to auto increment for each new record. Using this method you must have some value for each table column.

SQL - Insert into multiple tables

This concept isn't widely supported by open source database programs, however they do offer alternative methods to achieve the same goal. The idea is to insert similar record values into 2 or more tables with one statement. Using the example from above, we want to place Julie's information into our manager table as well as the general employee table.

INSERT ALL INTO employees (Lastname,Firstname,Title) 
VALUES('Davis','Julie','manager')
INTO manager (training,salary)
VALUES ('yadayada','22500');

• Go Back
• Continue