Insert Data into table with two columns, one is Identity column and another is RowVersion
Posted by Prashant on June 7, 2010
This post is from a discussion in one of our SQL Server training session with Mr. Rakesh Mishra. While discussing RowVersion data type, I have created a table with two columns one is of INT IDENTITY and another is of ROWVERSION type. Now the situations arises is how to insert data into that table?
Note: ROWVERSION which is auto generated, unique binary number within a database and generally used for version stamping in table rows.
In the ROWVERSION column we can’t explicitly insert data, so can we insert data into the INDENTITY column without making the IDENTITY_INSERT to ON?
Yes, we can do that and here is the way it can be done.
--Create the table for demo CREATE TABLE RowVersionTest(myID INT IDENTITY PRIMARY KEY, myRowVer ROWVERSION)
What if I will insert data into the IDENTITY column?
--Insert Data INSERT INTO RowVersionTest(myID) VALUES (1)
MESSAGE: Msg 544, Level 16, State 1, Line 3 Cannot insert explicit value for identity column in table 'RowVersionTest' when IDENTITY_INSERT is set to OFF.
The above error message is because, we have not we can’t insert data into a IDENTITY column till IDENTITY_INSERT is OFF. So here is the solution for this:
--Insert Data INSERT INTO RowVersionTest DEFAULT VALUES INSERT INTO RowVersionTest DEFAULT VALUES INSERT INTO RowVersionTest DEFAULT VALUES INSERT INTO RowVersionTest DEFAULT VALUES INSERT INTO RowVersionTest DEFAULT VALUES --Select Data SELECT * FROM #RowVersionTest
DROP TABLE #RowVersionTest
Please leave your valuable comments.