SQL Server INSERT INTO statement with examples

INSERT is used to add one or multiple rows of data into a table. It’s among the most important and frequently used queries in not only SQL Server but many other relational databases such as MySQL, PostgreSQL, SQLite, etc.

In this post, we are going to learn how to use the INSERT INTO statement in SQL Server (Transact-SQL) with examples.

The general syntax of INSERT INTO statement:

INSERT INTO table_name (column_name)
VALUES (value_set);

Note that the INTO keyword if totally optional, you can use it or not, it doesn’t matter.

Let’s take some example of using INSERT INTO statement.

You want to insert student info to the table students with four columns: id, first_name, last_name, state_code:

1. Insert rows with full data set

Insert one row of data

INSERT INTO students (id, first_name, last_name, state_code)
VALUES (34, 'Anna', 'Smith', 'CA');

In the case above, when you insert a new row with a full set of data for all columns, you might not need to specify the column name. So you can simply use this statement instead:

INSERT INTO students
VALUES (34, 'Anna', 'Smith', 'CA');

And perform inserting multiple rows:

INSERT INTO students
VALUES (34, 'Anna', 'Smith', 'CA')
          (35, 'Bob', 'Jonas', 'CA')
          (36, 'Nick', 'Jonas', 'CA');

2. Insert data for some specific columns

When you want to insert id, first_name, and last_name only, use this statement:

INSERT INTO students (id, first_name, last_name)
VALUES (34, 'Anna', 'Smith');

And do it for multiple rows:

INSERT INTO students  (id, first_name, last_name)
VALUES (34, 'Anna', 'Smith')
          (35, 'Bob', 'Jonas')
          (36, 'Nick', 'Jonas');

3. Bulk insert from another table

You can also insert multiple rows of data at the same time from another table using SELECT statement:

INSERT INTO table_a (col_1a, col_2a ....)
    SELECT col_1b, col_2b…
       FROM table_b;

Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple relational databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle… And it’s available for free!