find and solve || findandsolve.com
Please wait.....

SQL INSERT INTO Statement Example

SQL stands for Structured Query Language which is a standard language for storing, manipulating, and retrieving data from databases. In this tutorial will you teach how to use SQL in SQL Server for insert statements.


Structured Query Language (SQL)


SQL (Structured Query Language) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. 

SQL is the standard language for Relational Database management systems. All the Relational Database Management Systems (RDMS) like SQL Server, MS Access, Oracle, MySQL,  Sybase, Postgres, and Informix use SQL as their standard database language.


They are using different dialects, as given below

  • MS SQL Server using T-SQL,
  • Oracle using PL/SQL,
  • MS Access version of SQL is called JET SQL (native format) etc.



Introduction to SQL Server INSERT statement


To add one or more rows to a table, we use the INSERT statement. The following illustrates the most basic form of the INSERT statement as given below.


INSERT INTO <YourTableName> (<YourColumnList>) VALUES (<YourValueList>);


Let’s examine this syntax in more detail.

First, we specify the name of the table in which we want to insert data. Typically, we reference the table name by the schema name

for example Employee.EmployeeInfo where Employee is the schema (Database) name and EmployeeInfo is the table name.


Second, we specify a list of one or more columns in which we want to insert records. We must enclose the column list in parentheses ( ) and separate the columns by commas (,).

If a column of a table does not appear in the column list, SQL Server must be able to provide a value for insertion or the row cannot be inserted.


SQL Server automatically uses the given value for the column that is available in the table but does not appear in the column list of the INSERT statement:

  • The column has an IDENTITY property.
  • The column has a default value specified.
  • The current timestamp value, the data type of the column is a timestamp data type.
  • The NULL if the column is nullable.
  • The column is a computed column.


Third, we provide a list of values to be inserted in the VALUES clause. Each column in the column list must have a corresponding value in the value list. Also, we must enclose the value list in parentheses ().


If we are adding values for all the columns of the table, we do not need to specify the column names in the SQL query in Server. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax like as given below.


INSERT INTO <YourTableName> VALUES (<value1>, <value2>, <value3>, ...);


Parameters or Arguments


  • <YourTableName> : The table in which to insert the records.
  • <value1>,<value2>,<value3> : These are the columns in the table to insert values.


Using INSERT Statement to Insert One Record


The simplest way to use the INSERT statement is to insert one record into a table using the VALUES keyword. Let's look at an example of how to do this in SQL.

In the given example, we have a table called EmployeeInfo with the following data:

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10


Let's insert a new EmployeeInfo record. Enter the given SQL statement


INSERT INTO EmployeeInfo
 (
FirstName,
LastName,
Address,
Age

VALUES 
(
'Rahul',
'Sharma',
'Pokhara',
23
);


There will be 1 record inserted in EmployeeInfo Table. Select the data from the EmployeeInfo table again as given below.


SELECT * FROM EmployeeInfo;


These are the results that you should see:

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10
RahulSharmaPokhara23


The given above example would insert one record into the EmployeeInfo table. This new record would have a FirstName 'Rahul', LastName 'Sharma' Address 'Pokhara', and an Age of 23.


In the given example, because we are providing values for all of the columns in the EmployeeInfo table, we omit the column names and instead write the INSERT statement as given below.


INSERT INTO EmployeeInfo 
VALUES
(
 'Rahul',
 'Sharma',
 'Pokhara',
 23
);


However, this is dangerous to do for 2 reasons. First, the INSERT statement will error if additional columns are added to the EmployeeInfo table. And last on, the data will be inserted into the wrong columns if the order of the columns changes in the table. So as a general rule, it is better to list the column names in the INSERT statement.


Insert Data Only in Specified Columns


It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "FirstName", "LastName", and "Address" columns.


INSERT INTO EmployeeInfo
 (
  FirstName,
  LastName,
  Address,  
)
VALUES
(
 'Rahul',
 'Sharma',
 'Pokhara',
);


The selection from the "EmployeeInfo" table will now look like as given below:

FirstNameLastNameAddressAge
SylviaNeupaneKathmandu10
RahulSharmaPokhara23
DebinThapaMirmeenull