SQL INSERT Statement

SQL INSERT statement allows you to insert one or more rows into a table. The SQL INSERT statement syntax is defined in SQL standard as follows :

INSERT INTO {table_or_view_name}
[({column_name},...)]
{ {VALUES ({literal} | 
           {expression} |
           NULL |
           DEFAULT,...)} | 
  {{select_statement}} }

For example if you want to insert a new record into employees table you can use the query like this:

INSERT INTO employees(name,salary)  
VALUES('anthony','2000')

In case you have to insert null or default value into columns of database table you can you NULL or DEFAULT instead of your values. Here is an example of inserting NULL value into salary column of employees table:

INSERT INTO employees(name,salary)  
VALUES('anthony',NULL)

and an example of inserting default value if you specified DEFAULT value for the column in RDBMS:

INSERT INTO employees(name,salary)  
VALUES('anthony',DEFAULT)

One of powerful of SQL INSERT statement is you can use data from other tables to insert into your table using SQL SELECT statement. This example demonstrates inserting all values from employees table, which salary column greater or equal 2000$, into a temporary table.

INSERT INTO temp_table(name,salary)  
SELECT name,salary  FROM employees  
WHERE salary > 2000

One of the most important thing to remember when using SQL INSERT statement is it has to obey certain rules and restrictions of database schema. For example, you try to insert NULL value into column which is NOT NULL. You should ensure the constraints by your application logic, and don't use CHECK CONSTRAINTS if it is not necessary because it degrades performance of bulk copy operations.