SQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

There are two ways to insert data in a table:

  1. By SQL insert into statement
    1. By specifying column names
    2. Without specifying column names
  2. By SQL insert into select statement

Inserting data directly into a table

You can insert a row in the table by using SQL INSERT INTO command. But there are 2 ways to do this.

You can specify or ignore the column names while using INSERT INTO statement.

To insert partial column values, you must have to specify the column names. But if you want to insert all the column values, you can specify or ignore the column names.

If you specify the column names, syntax of the insert into statement will be as follows:


INSERT INTO TABLE_NAME   

[(col1, col2, col3,.... col N)]   

VALUES (value1, value2, value 3, .... Value N);         

					

But, If you ignore the column names, syntax of the insert into statement will be as follows:


INSERT INTO TABLE_NAME   

VALUES (value1, value2, value 3, .... Value N);        

					

					Note: At the time of inserting a row into table, if you add values for all columns then there is no need to specify the column name in SQL INSERT query. Moreover, you must be sure that you are entering the values in the same order as the columns exist.

					

SQL INSERT INTO VALUE

There are two ways to insert values in a table.

In the first method there is no need to specify the column name where the data will be inserted, you need only their values.

INSERT INTO table_name   

VALUES (value1, value2, value3....);  

The second method specifies both the column name and values which you want to insert.

INSERT INTO table_name (column1, column2, column3....)



VALUES (value1, value2, value3.....);

SQL INSERT MULTIPLE ROWS

Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently developers have to write multiple insert statement when they insert values in a table. It is not only boring, also time consuming. To get rid from this you should try this syntax. Actually there are three different methods to insert multiple values into a single table.

  1. Traditional method (simple insert)
  2. SQL insert select
  3. SQL server 2008+ Row Construction
CREATE TABLE student (ID INT VALUE VARCHAR (100));
Note:Row Constructor is a new feature for SQL Server 2008. It is not supported by SQL Server 2005.