SQL INSERT Statement

The SQL INSERT statement adds new records into tables or to load data into SQL tables. The syntax of this statement is

INSERT INTO [DestinationTable] ([Column1], [Column2],..., [ColumnN])
VALUES ([Column1_Value], [Column2_Value],..., [ColumnN_Value])

DestinationTable: Fully qualifies Table name to put records
Column1…..ColumnN: Choose the number of columns from the tables.
Column1_Value…..ColumnN_Value: Values that you want to put. For example, Column1_Value loaded for Column1. If you omit the field names, you must specify the column_values (field values) in the order defined by the destination table structure

We can write this Sql Server Insert Statement as,

INSERT INTO [DestinationTable]
VALUES ([Column1_Value], [Column2_Value],..., [ColumnN_Value])

This table holds two records, and we will perform the insert operations on it.

SQL INSERT Statement Example

This SQL Insert Statement example put a new record into a Table in a more traditional way. Here, we haven’t loaded the ID value because it is an identity column, and it will be updated automatically

INSERT INTO [dbo].[SQLInsert] (
	[FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
VALUES ('Tutorial', 'Gateway', 'Education', 10000, 200)

If we are loading data for all the Columns into target, then we can ignore the column names of the destination (Syntax 2). It means the above SQL Server Insert Statement can also write as:

INSERT INTO [dbo].[SQLInsert] 
VALUES ('SQL', 'Programming', 'Database', 50000, 1800)

TIP: It is not good practice to ignore the column names. So, always provide the column names in SQL Server.

Above query will place data into [FirstName], [LastName], [Occupation], [YearlyIncome], and [Sales] columns. Whenever you omit the field names, you must specify the column_values (field values) in the order defined by the destination table structure. It means SQL value will put in [FirstName] column etc.

How to Insert Multiple records into a Table?

This Sql Server Insert Statement example load multiple records into a Table in a more traditional way.

INSERT INTO [SQLInsert] 
VALUES ('Virat', 'Kohli', 'Cricket', 15700, 800)
INSERT INTO [dbo].[SQLInsert] 
VALUES ('Rohit', 'Sharma', 'Cricket', 15000, 500)

The oldest way to load multiple records.

INSERT INTO [SQLInsert] 
SELECT 'John', 'Levi', 'Professional', 75700, 1900
UNION ALL
SELECT 'Doe', 'Martin', 'Management', 95000, 5600

Here, we use the most popular way to put multiple records into the Table. This SQL Insert Statement will work in SQL Server 2008 and later versions

INSERT INTO [SQLInsert] 
VALUES ('Imran', 'Khan', 'Skilled Professional', 15900, 100)
      ,('Doe', 'Lara', 'Management', 15000, 60)
      ,('Ramesh', 'Kumar', 'Professional', 65000, 630)

How to Insert few records into a table?

In this Sql Server Insert Statement example, we put few Records into a table. Remember, When you are loading few records into the destination, you must specify the column names.

INSERT INTO [dbo].[SQLInsert] (
	 [FirstName], [Occupation], [Sales])
VALUES ('Insert', 'SQL Tutorials', 1225)

Above query will put data into [FirstName], [Occupation], and [Sales] columns. NULL values are added in the remaining columns.

Insert Into Select Statement

The Employ we use for the query

In this SQL Insert Statement example, we select rows from the Employ and load them into a destination. Here, we restrict the rows using the WHERE Clause.

INSERT INTO [dbo].[SQLInsert] 
	SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
	FROM Employ
	WHERE ID > 2

Above query add [FirstName], [LastName], [Occupation], [YearlyIncome], and [Sales] columns from the source where [ID] value is greater than 2 into the destination.

Please refer to the INSERTINTO Select Statement article. Let us see whether the selected data loaded into the destination or not.