December 12, 2005 at 11:55 am
I have an insert statement that looks like so:
insert into mytable (firstname, lastname, timestamp)
VALUES ('John', 'Doe', ???????)
where the column timestamp is datatype timestamp and is giving me problems. Other records in the database list the timestamp as a varbinary that looks like:
0x0000000000013D96
Can anyone explain what I need to do in my ?????? above to allow me to create a record please? This is a table I inherited and cannot change any columns or datatypes.
Many thanks in advance!
December 12, 2005 at 12:13 pm
How about just this:
insert into mytable (firstname, lastname)
VALUES ('John', 'Doe')
The system will set the timestamp value for you.
ron
December 12, 2005 at 12:21 pm
Thanks for the reply. I tried taking it out but got the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Does it make any difference that the timestamp column is actually smack in the middle of 12 total columns? The actual structure is more like:
insert into mytable (firstname, lastname, updatedate, timestamp, field1, field2)
VALUES ('John', 'Doe', '2005-12-12 12:19:36.703', ??????, NULL, NULL)
Thanks again!
December 12, 2005 at 12:22 pm
There may be a big problem. What do you want your timestamp column to do? What datatype is the timestamp column - is it TIMESTAMP or DATETIME?
Timestamp datatype is not a DATE & TIME value. From the BOL:
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
So, if your Timestamp column is of the TIMESTAMP datatype, you won't be able to insert date and time values into it.
-SQLBill
December 12, 2005 at 12:38 pm
The position of the column within the table does not matter. If the datatype is "timestamp" then do not reference the column name in the list of columns and do not try to set any value in the values clause of the the insert statement. The system will always update the value of a timestamp column upon Insert or Update.
You might want to post the table definition and exact insert statment that is giving you a problem.
ron
February 27, 2007 at 12:15 am
Hi,
Use
insert into mytable (firstname, lastname,timestamp_column_name)
VALUES ('John', 'Doe',default)
Regards,
Rajdeep
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply