SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

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

--Cleanup
DROP TABLE #RowVersionTest

Please leave your valuable comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: