In this chapter “Insert statement“, we are going to discuss T-SQL’s Insert statement which is used to add new records in a table. Whenever, we need to add a record in a table, INSERT T-SQL statement is used. We can insert single or multiple records at a time using INSERT statement. INSERT statement can be used in two ways as below:
- To insert fixed (hard-code) values
- To insert the result of a SELECT statement
Let’s discuss each of them in more detail with the help of example.
To insert fixed (hard-code) values
To insert fixed values in the table, below is the syntax:
Syntax:
INSERT INTO [<schema-name>.]<table-name>
[(column-1, column-2, column-3, …….., column-n)]
VALUES
(value-1, value-2, value-3, …….., value-n)
Where,
INSERT INTO is used to declare a new INSERT statement.
[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists in the default schema that is “dbo”.
<table-name> is the name of the table in which records needs to be inserted.
[(column-1, column-2, column-3, …….., column-n)] is the comma separated list of the columns in which values needs to be inserted. This part can be optional if we want to insert the values in all columns of a table, but, in this scenario, the order of the values must be aligned with the order of the columns defined in the table. That is, value-1 for column-1, value-2 for column-2, value-3 for column-3 and so on.
As a best practice, we should always specify the column lists explicitly in INSERT statement, so that changing the order of the columns in the base table will not impact the INSERT queries which will avoid such kind of errors during insert operations. If columns 1 is of int data type and column 2 is of varchar data type, for some reason in future if anyone change the order of the base table, any insert statement written without specifying the list of columns will raise an error as all varchar values can not be converted into an integer value.
VALUES is used to specify the list of values within parentheses ().
(value-1, value-2, value-3, …….., value-n) is the comma separated list of the values specified in chronological order of the columns which are specified in the column list section. Values are the actual data which needs to be inserted in the columns as a cell value. If the given value is of character, string or binary data type, it must be enclosed within single quotes like; ‘ABC’, but, for integer and decimal data types, enclosing these values within single quotes is optional.
Note: The optional parts of the syntax have been wrapped inside the brackets as [<schema-name>.].
During inserting data into a table, we must have to supply the values for each and every column of the table. However, the columns created with a default value (using a default constraint) can be excluded in this list. Although, we can overwrite these default values by putting the column name and associated value for that column in INSERT INTO statement.
Lets have few examples:
First, create a table with below script:
CREATE TABLE dbo.Employee ( EmpId INT NOT NULL, EmpName VARCHAR(256) NOT NULL, Address VARCHAR(512) )
To add rows in the above table, we can use an INSERT statements as below:
INSERT INTO dbo.Employee (EmpId, EmpName, Address) VALUES (100, 'Employee 1', 'Delhi')
In above example, we can see that the text values are enclosed within single quotes.
Row constructor:
Row constructor is introduced in SQL Server 2008 and is used to insert multiple rows (by defining the values for multiple rows in VALUES part separated by comma) in one INSERT statement.
INSERT INTO dbo.Employee (EmpId, EmpName, Address) VALUES (200, 'Employee 2', 'Delhi'), (300, 'Employee 3', 'Delhi')
We can specify values for multiple rows separated by comma enclosed within parentheses.
Note: We can insert only up to 1000 rows using row constructor and constructing more than 1000 rows will raise an error.
We can not insert values in an identity column using INSERT INTO statement and the identity column will automatically get its value as per the specified seed value and to insert a value in identity column explicitly, we need to use a T-SQL command “SET IDENTITY_INSERT <schema-name>.<table-name> ON” on the table.
To insert the result of a SELECT statement
T-SQL also provides a way to insert the output of a T-SQL SELECT statement directly into a table. The syntax is as below:
Syntax:
INSERT INTO [<schema-name>.]<table-name>
[(column-1, column-2, column-3, …….., column-n)]
SELECT column-1, column-2, column-3, …….., column-n
FROM [<schema-name>.]<table-name>
[WHERE <filter>]
Where,
INSERT INTO is used to declare a new INSERT statement.
[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists in the default schema that is “dbo”.
<table-name> is the name of the table in which records needs to be inserted.
[(column-1, column-2, column-3, …….., column-n)] is the comma separated list of the columns in which values needs to be inserted. This part can be optional if we want to insert the values in all columns of a table, but, in this scenario, the order of the values must be aligned with the order of the columns defined in the table. That is, value-1 for column-1, value-2 for column-2, value-3 for column-3 and so on. All other details have already been explained in above syntax definition.
SELECT is the T-SQL select statement which is used to extract the rows from one or multiple tables and views.
column-1, column-2, column-3, …….., column-n is the list of the columns and must be in the order in which the column names are defined in column list section. If list of the columns are not defined, it must be in the order in which columns of the table are defined.
FROM [<schema-name>.]<table-name> is used to define the name of the object which could be a table, view or a table valued function from which rows are being extracted. However, in general, we get data from multiple objects using SQL JOINS.
WHERE <filter> is used to apply a filter to extract only rows based on certain conditions from the source object. This is an optional part.
Example:
INSERT INTO dbo.Employee (EmpId, EmpName, Address) SELECT TOP 10 (100 * (3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)))) AS EmpId, 'Employee ' + CAST((3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS VARCHAR(10)) AS EmpName, 'Delhi' AS Address FROM sys.objects
In above SELECT statement, we have used ROW_NUMBER() windows function to generate a sequence for each row whereas sys.objects, a system view, is used to generate 10 records using TOP statement in the select query.
Also, we can see the aliases used for columns in the SELECT list. These aliases are same as the table column name, but this is optional and if we do not put these aliases here, it is not going to make any impact on the insert operation.
Thanks for the reading and please rate this post. If you like this post, please do share this on your social network.
