Insert Data into Tables in SQL Server using INSERT Statement

The INSERT INTO statement is used to insert single or multiple records into a table in the SQL Server database.

Syntax:

INSERT INTO table_name(column_name1, column_name2...) 
VALUES(column1_value, column2_value...);

Here, we will insert data into the following Employee table which we created in the Create Table chapter.

The following INSERT INTO statement will insert a single row in all columns of the above Employee table in the SQL Server database.

T-SQL: Insert Data

INSERT INTO Employee(FirstName, LastName, EMail, Phone, HireDate, Salary)
VALUES('John','King','[email protected]','123.123.0000','01-01-2015', 33000);

Note that EmployeeId column is an identity column, so the values will be auto-generated on each insert statement. So, EmployeeId column is not included in the above insert statement.

To see the inserted data, execute the Select * from Employee; query in the query editor, as shown below.

Insert Values to All Columns

To insert values to all columns of a table, you don't need to specify column names with the table name. Specify the values for each column in a sequence as they appear in the table, as shown below.

T-SQL: Insert Data

INSERT INTO Employee
VALUES('Neena','Kochhar','[email protected]','123.000.000','05-12-2018',17000);

Note:

Any change in the sequence, the number of values, or its data type may result in an error or incorrect data.

Insert Values to Specific Columns

To insert data into specific columns, specify the column names in the parenthesis. Make sure other columns allow null values; otherwise, an error will be raise.

The following will insert data in FirstName, and LastName columns only.

SQL Script: Insert Data to Specific Columns

INSERT INTO Employee(FirstName, LastName)
VALUES('James','Bond');

Note:

You must insert data to all NOT NULL columns; otherwise, it will raise an error.

Insert Multiple Records

Insert multiple records in a single INSERT INTO statement by having multiple records in parenthesis after VALUES. The following will insert two records in the Employee table in SQL Server, MySQL, PostgreSQL, SQLite database.

T-SQL: Insert Multiple Records

INSERT INTO Employee 
VALUES 
('Kevin','Weiss','[email protected]','123.123.12','08-10-2019',17000),
('Lex','De Haan','[email protected]','123.123.13','05-05-2019',15000),
('Laura','Bissot','[email protected]','123.123.15','02-08-2019',40000);

To insert multiple records into specific columns, specify the column names in the parenthesis, as shown below.

T-SQL: Insert Multiple Records in Specific Columns

INSERT INTO Employee(FirstName, LastName) 
VALUES 
('Kevin','Weiss'),
('Lex','De Haan'),
('Laura','Bissot');

Now, execute the Select * from Employee query will display the following result.