How To Insert Data in SQL Server

Sometimes you may need to insert one or more rows in SQL Server. In this article, we will look at how to insert data in SQL Server. You can use it to insert multiple rows in SQL Server. You can also use it to insert JSON data in SQL Server.

How To Insert Data in SQL Server

We will insert data into SQL Server using INSERT statement. There are two syntax variations for INSERT statement.

insert into table_name(column1, column2, ...)
values(value1, value2, ...);

or

insert into table_name
values(value1, value2, ...);

In the first statement you need to specify table name, column names and set of values to be inserted, in a comma-separated manner.

In the second statement, you need not specify column names. In this case, you need to ensure that order of values is same as order of columns in table.

Let us say you have the following table sales(id, order_date, amount)

# create table sales(id int, order_date date, amount int);

Here is the SQL query to insert single row in SQL Server.

# insert into sales(id, order_date, amount)
values(1,'2020-12-01',100);

# select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    100 |
+------+------------+--------+

Also read : How to Create View in SQL Server

Insert Multiple Rows

Here is the SQL query to insert multiple rows in SQL Server.

# insert into sales(id, order_date, amount)
values(2, '2020-12-02',250),
(3, '2020-12-03',350);

# select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    100 |
|    2 | 2020-12-02 |    250 |
|    3 | 2020-12-03 |    350 |
+------+------------+--------+

Also read : How to Add default value in SQL Server

Insert Into Select

You can also use the result of a SELECT statement to insert data into SQL server. Here is the syntax

# insert into table_name(column1, column2, ...)
select ...

In the above query, you need to specify table name and select query whose result you want to use to populate your table. You can optionally specify list of column names along with table name.

Here is an example to insert data into sales table from another table orders

# insert into sales
  select * from orders
  where id>100;

This is a good way to copy data from one table to another in SQL Server.

Also read : How to Drop View in SQL Server

Insert JSON Data in SQL Server

You can also insert JSON data into a JSON column in SQL Server. Just wrap the JSON data in single quotes.

# insert into sales(dates) values('{ "start_date": "2020-12-02 14:05:25", "end_date": "2020-12-02 10:57:15.127" }');

As you can see, there are numerous ways to insert data in SQL server.