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”:
- 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.
- 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:
- A member of sysadmin fixed server role
- A member of db_owner fixed database role
- A member of db_ddladmin fixed database role
- 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
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
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.