SQL Server

How to Insert Data in SQL Server

Here's the shortest and easiest way to insert data into a SQL Server table. You only have to specify the values, but you have to pass all values in order. If you have 10 columns, you have to specify 10 values.

-- assuming the sessions table has only three columns:
-- id, start_date, and end_date, and category, in that order
insert into sessions values (11, '2020-02-02 14:05:15.400', '2020-02-03 14:25:15.400', 1);

It's optional, but specifying a column list before the VALUES keyword is highly recommended:

insert into sessions (id, start_date, end_date, category) 
values (12, '2020-02-02 14:05:15.400', '2020-02-04 16:57:53.653', 2);

By specifying a column list, you don't have to remember the column order as defined in the table:

insert into sessions (id, category, start_date, end_date)
values (12, 2, '2020-02-02 14:05:15.400', '2020-02-04 16:57:53.653');

Having a column list has more advantages:

  • You don't have to specify a value for all columns, just the required ones.
  • In case there are many columns, it is easier to match a value to the intended column when you see it in the statement, rather than having to look at the table definition.
  • INSERT statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

If you have many columns, but only want to specify some:

insert into sessions(id, start_date) values (12, '2020-02-02 14:05:15.400');

Inserting Multiple Rows

You can insert multiple rows in one INSERT statement by having multiple sets of values enclosed in parentheses:

insert into sessions (id, start_date, end_date, category)
values
  (13, '2020-02-02 15:05:15.400','2020-02-03 15:14:30.400', 3),
  (14, '2020-02-02 17:07:16.300','2020-02-02 19:10:15.400', 4),
  (15, '2020-02-03 15:05:45.127','2020-02-04 18:05:15.400', 2);

You can also use INSERT with a SELECT command to copy data from an existing table. Note the VALUES keyword is omitted:

insert into sessions_dm  (id, start_date, end_date, category)
select *
from sessions
where id > 10;

Inserting JSON Values

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

insert into sessions(dates) values('{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }');

See also: our tutorial on querying JSON columns.

Handling Conflicts/Duplicates

If inserting a row would violate a unique constraint, there is a way to handle it.

--if you want to insert the row if doesn't exists, otherwise ignore 
insert into sessions(id, dates)
select 
  13, 
  '{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }'
where not exists (select id from sessions where id=13);


--if you want to update the row if exists, otherwise insert:
if exists (select * from sessions where id=14)
  update sessions
  set dates=null
  where id=14;
else
  insert sessions (id, dates)
  values (14, '{ "start_date": "2020-02-02 14:05:15.400", "end_date": "2020-02-02 14:57:45.127" }' );