How to Insert Values in a Table with just an Identity Column


When we make an identity column in a table then for every record inserted in the table the identity column auto increments the value without specifying the identity column in the Insert command. What if we have to insert record in this column manually using a SQL command? So in this article we will just see how to achieve this.

We will create a table with just an identity column. Once the table is created we will try to insert records in this table using SQL query.

Write the following script in SQL Server to create a table.

  1. Create table tblInsertintoIdentityColumn  
  2. (  
  3. Id int primary key identity  
  4. )  
Execute the above SQL code to create a table in the selected database. Execute the following query to check the contents in the table. As we haven’t inserted any records in this table, so the query will just return the table’s schema.
  1. Select * from tblInsertintoIdentityColumn  

Now we will insert value in this table. Write the following query.
  1. Insert into tblInsertintoIdentityColumn default values  
Execute the preceding SQL command.

As we can see the command is successfully executed. So let us check the records in the table.

Run a select query and check the records in the table.

And so on we can insert as many records in the Identity column in a table.