Insert problem with timestamp column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Insert problem with timestamp column

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-14 : 02:12:23

I have one table test with column timestamp.
create table test
(
timestamp timestamp null
)
I am trying to insert the value in this column.
insert into test
values (0x000000000008E189)
but I got the following error.

Server: Msg 273, Level 16, State 1, Line 1
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.

please tell me how to resolve this problem

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-14 : 03:06:47

I think you are misunderstanding the timestamp datatype. This column will be updated anytime a row is inserted or updated. It is used as a way to determine if the values in a row have changed. Timestamp is also referred to as rowversion, which is a much better description of what it is used for.

bottom line is you don't insert anything into this type of column. SQL Server updates it automatically.

-ec

shubhada
Posting Yak Master

117 Posts

Posted - 2006-08-14 : 03:30:14

actually i have one table which is modified with new column.
but i want the old data in modifed table
ex : test table with column
col1
col2
timestamp

modified table test_new
col1
col2
col3
col4
timestamp..

SQLTeam

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-14 : 10:02:38

quote:
Originally posted by shubhada

actually i have one table which is modified with new column.
but i want the old data in modifed table
ex : test table with column
col1
col2
timestamp

modified table test_new
col1
col2
col3
col4
timestamp..

SQLTeam

use the binary datatype then. like i said before, timestamp is automatically updated by SQL Server. You do not directly insert or update a column that is a timestamp datatype.

-ec

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 10:10:04

shubhada,

U can create the new table with a Datetime data type and insert all records from first table. Then (if that type of data moving is not done again) u can change the DateTime column to TimeStamp. If not keep the DateTime column as it is (the type) and by a trigger or some such, fill that column with data as necessary.

As ec says, TimeStamp data type is used for specific purpose and u can learn more about this in BOL.

Also I coundn't find any reason for ur SQL


insert into test
values (0x000000000008E189)

y r u trying to enter, binary data to a column of TimeStamp data type ?

Srinika

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-14 : 10:41:42

srinika, timestamp is a binary datatype. it has nothing to do with datetime values.

-ec

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 11:22:05

quote:
Originally posted by eyechart

srinika, timestamp is a binary datatype. it has nothing to do with datetime values.
-ec

Thanks EC for pointing out my mistake (thats a huge mistake in my side).
Yes, I learned about it as well.

http://www.sqlteam.com/item.asp?ItemID=1410

Thanks again, and shubhada, sorry for the non-valid info.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources