Apr 3, 2012

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.


Suppose we have created OrderHistory table in current database.

CREATE TABLE OrderHistory(
    OrderId BIGINT PRIMARY KEY,
    OrderDate TIMESTAMP
)

Now if will try to insert some records into the OrderHistory table:

INSERT INTO  OrderHistory(OrderId,OrderDate) VALUES(100,GETDATE()) 

We will get error message :

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Cause: We cannot insert explicit value in timestamp column in sql server

Solution:

Correct way to insert :

INSERT INTO  OrderHistory(OrderId,OrderDate) VALUES(100,DEFAULT) 

Or

INSERT INTO  OrderHistory(OrderId) VALUES(100)  

1 comment:

  1. pluque4/11/2012

    What about if I wishes to insert using implicit column list like:

    INSERT INTO OrderHistory VALUES(100)

    well I tried it and it prompts error 213:
    Msg 213, Level 16, State 1, Line 3
    Column name or number of supplied values does not match table definition.

    We all know that when specifying column names (w/o the timestamp one), it will insert the value of @@DBTS.

    so why can't we user insert that way: INSERT INTO OrderHistory VALUES(100)

    ReplyDelete