Insert into table with one identity column only

One of my friend asked me a question that “How we can insert values in a table with only one column which is identity, without using IDENTITY_INSERT ON?”. In this post “Insert into a table with one identity column only”, I am going to share a nice method to achieve this.

We know that using “SET IDENTITY_INSERT TableName ON” command, we can easily insert rows in an identity column. As per MSDN, we need to keep few things in mind related to “IDENTITY_INSERT ON”:

1. Only one table can have IDENTITY_INSERT as ON in one session at a time. We must have to use “SET IDENTITY_INSERT TableName OFF” to use IDENTITY_INSERT on other tables.
2. To execute IDENTITY_INSERT on a table, a user must own the table or has ALTER permission on the table. Only below types of user can execute IDENTITY_INSERT on a table:
   1. A member of sysadmin fixed server role
   2. A member of db_owner fixed database role
   3. A member of db_ddladmin fixed database role
   4. A user with ALTER permission on table

Users with only above permissions can execute IDENTITY_INSERT on a table, otherwise they get a permission error.

To know more about IDENTITY_INSERT, click here.

Now, we are going to be familiar with another approach to execute INSERT command in this scenario which is not tied up with above limitations.

INSERT Command with DEFAULT VALUES

Use below script to create and insert data into table with one identity column only:

--Create a dummy table with one identity column only
CREATE TABLE dbo.TestIdentity
(
RowId INT IDENTITY(1, 1)
)
GO
--Insert into table with one identity column only
INSERT INTO dbo.TestIdentity DEFAULT VALUES
GO
--Check data inserted in table
SELECT * FROM TestIdentity
GO

Output

In above script, table “TestIdentity” has only one column which is an identity also. We have used “INSERT INTO dbo.TestIdentity DEFAULT VALUES” command, to insert identity value in the column “RowId”. We can also use the same command with a table with multiple columns created with default values. Have a look on below script:

--Create a dummy table with one identity column only
CREATE TABLE dbo.TestIdentity_MultipleColumn
(
RowId INT IDENTITY(1, 1),
RowInsertedOn DATETIME DEFAULT GETDATE(),
IsRowValid BIT DEFAULT 1
)
GO
--Insert into table with one identity column only
INSERT INTO dbo.TestIdentity_MultipleColumn DEFAULT VALUES
GO
--Check data inserted in table
SELECT * FROM TestIdentity_MultipleColumn
GO

Output

In the above script, we have a table “TestIdentity_MultipleColumn” with columns RowId, RowInsertedOn and IsRowValid. All three columns have created with default values. To insert record in this table, we can use “INSERT INTO dbo.TestIdentity_MultipleColumn DEFAULT VALUES” command also.

At the end, I would like to have few silly questions. If you are interested, please share your answers in comment section of this post.

What will be the output of below SQL statements?

SELECT 1 + '1'
SELECT 1 + '1.0'
SELECT 1.0 + '1.0'

Thanks for your reading. Please do share and rate this post if you really like this and don’t forget to share your comments here.