SQL - Force INSERT into datetime column

[quakefiend420]

UPDATE:i fail at error comprehension, mixed up timestamp and datetime datatypes lol


I'm archiving data from a single table to a 2nd DB. I am unable to directly INSERT the timestamp values because SQL Server wants to generate them itself.

Is there any way to force SQL Server to allow me to insert the values that I pull from the main table? There are a few datetime columns that I need to stay the same, not get updated with the current server time.

INSERT INTO ARCHIVEDB.dbo.TABLE
SELECT * FROM TABLE WHERE Column = @VariableSetElsewhere

DELETE FROM TABLE WHERE Column = @VariableSetElseWhere

That is the basic syntax of my statement.

All the research I've done suggests setting a default value to override, which would be fine if i were inserting a small number of rows...but this script will move 20K rows at a time, it's simply not feasible to specify a default value for each row that I need to insert.

 

[brandonb]

There is nothing in SQL which forces a timestamp into a column. There has to be a constraint or something on the table that you should disable (Default value for example of GETDATE())

 

[quakefiend420]

There is nothing in SQL which forces a timestamp into a column. There has to be a constraint or something on the table that you should disable (Default value for example of GETDATE())

Here's the error I'm getting:

Msg 273, Level 16, State 1, Procedure PROCNAME, Line 20
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.

 

[quakefiend420]

user error, nothing to see here

 

[KIAman]

http://msdn.microsoft.com/en-us/library/aa260631(v=sql.80).aspx

Rewrite your process to exclude inserting timestamp.

Once the data is inserted into the new table, timestamp will be automatically generated and won't be the same as from the old table.

Considering you are simply archiving that data and you don't want to lose timestamp, change the timestamp column from your archive table to a varchar and do a convert from of the timestamp from the original table.

 

[Mark R]

On SQL server, the timestamp is not intended to be manipulated or accessed by user applications. It is little more than a number that records the order in which rows are modified. Every time, SQL server modifies a row, it increments the "timestamp" - which isn't a time stamp at all, as it doesn't contain any record of the time or date.

If you must archive it, then it can be inserted into a varbinary field. This way, you can compare the timestamp in the live table to a backup copy - if they don't match, then you know that the row had been updated at some point after the copy was taken (even if the data was subsequently changed back).

If you make a field a 'timestamp' data type, then the 'time' stamping behavior is automatic and cannot be overriden.