Learn SQL: Microsoft SQL Server - Episode 13: Insert new records in a Table
Tags: sql, database, programming, analytics
Published: 2021-12-23 17:01:49


![greetings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rre6ht67x43tvsgs6cku.gif)

In this episode we will be discussing a key concept of SQL and database administration, we will be inserting a new record into a table.

We will be using the AdventureWorks database as usual, here we will be inserting a record into the **HumanResourcesDepartment** table.

![12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ozjyarh41n3jbksgk8f9.gif)

We first need to see which columns are available in this table. Inside the **HumanResourcesDepartment** table there are four columns, namely the ``DepartmentID``, ``Name``, ``GroupName`` and ``ModifiedDate`` columns respectively.

These are the columns we will be inserting records, we will be performing these tasks using a SQL statement.

![see-columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dx6qbh6ihrl0t9302dxp.gif)

In most cases, the <u>primary key</u> **(PK)** column does not require an explicit value to be entered. This value is <u>automatically generated</u> from the system.

![11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2q4x2de1htz2gh0ijkaf.gif)

To determine whether we need to add a value to a specific column or not, follow these steps in <abbr title="SQL SERVER MANAGEMENT STUDIO">SMSS</abbr>.

**Right-click** on the table-name and click on ``design``. When we click on ``design`` we will see all the column names, their data types and whether they allow _NULL_ values or not.

![4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2wfl1ta95cun33z1ofdp.gif) 

Below we will see a ``Column Properties`` window. When we click on our different columns above, we see the column properties below change.

We click on the **DepartmentID** column; next in the ``Column Properties`` window scroll down until we see **Identity Specification**. We expand this by clicking on the arrow on the **left-side**.

These values indicate that this column is in fact an **identity** column. ``Identity Increment`` means the identity column increases by 1 every-time a new value is entered and the ``Identity Seed`` means the starting value was 1.

![6](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mjwkpuu9yynxdco8tkxx.gif) 

**In short, every-time we add a new record; we will automatically generate a new value, which will be one higher than the previous highest value.**

![column-properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/17f2qvvtbc3f6t36eigk.gif)

We could check the other columns to determine if they are **Identity** columns, however they are not number values and additionally they do not increment, therefore it is safe to assume that **DepartmentID** column is the only column that is **identity** in this table.

>However we should double-check them to make absolutely certain.

![1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/de04e724pcf9q0j9r30e.gif)

#### Insert a new record

The structure of an <u>insert statement</u> is a bit different from what we have seen thus far. 

```
insert into [Table-Name] (column-name/s)
values (values to insert into sql)
```

For example, to insert a new record in the **HumanResourcesDepartment** table we can write it as follows.

```
insert into [HumanResources].[Department] (Name, GroupName, ModifiedDate)
values ('Learning', 'Growth and Education', getdate())
```
>Notice how the values in parenthesis, **(column-names/s)** and **(values to insert into sql)** need to correspond.

This means ``Name`` corresponds to **'Learning'**; ``GroupName`` corresponds to **'Growth and Education'** and ``ModifiedDate`` corresponds to **getdate()**.

**Getdate** is a special ``built-in`` function provided by <abbr title="SQL SERVER MANAGEMENT STUDIO">SMSS</abbr> and allows us to get the current date.

![11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2q4x2de1htz2gh0ijkaf.gif)

Let’s see this in action within <abbr title="SQL SERVER MANAGEMENT STUDIO">SMSS</abbr>.

![inserting-records](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rhegcuayp5ejxr7k4yqu.gif)

Once we execute this SQL query we get the message ``1 row affected`` followed by the time that the query executed.

We can perform a **SELECT** statement to confirm our record has been inserted into the **HumanResourcesDepartment** table.

![added-record](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/876w1ie02nc55jdzaxlz.gif)

There you have it, this is how we insert a new record into a table.

![cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/54n8oyp2u5oohem46ysu.png)

>Learn continually - there's always "one more thing" to learn.
-Steve Jobs

![Yoga-monkey](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p803ofktm85thr19t2ui.gif)