SQL Server 2K - Insert explicit value for timestamp column?

Question

Hey all,

I'm curently working on a project where I need to transfer data (with some minor transformations) from one database to another. Everything was going just fine when I hit my first Timestamp column...

It seems SQL Server doesn't want me to use explicit values for this type of column and it, instead, wants to generate its own.

If there a way, using T-SQL, I can get this data from one table to another?

Thanks,
Sonny

Answer 1

No.

• It's a unique (at the database level) binary(8) value
• The value would have no meaning in a different database
• The DB engine maintains it and increments accordingly

I just checked with a famous data compare tool that says it can compare them but not synch them.

Answer 2

Note: The name timestamp is deprecated, the new (and more accurate) name is rowversion.

Two answers:

No: SQL Server columns of type rowversion are always generated and set by the database server.

Yes: If you use a different datatype in the target table. The values read from a rowversion column can be stored in a varbinary column. So you could create a varbinary column in the new table to hold the values from the old table. Of course, going forward this varbinary column would not get updated automatically like a rowversion column would.

Answer 3

I wouldn't worry about that columns contents differing between source and target databases.

I am assuming by "Data" you pretty much means stuff that actual business users need or want.

The timestamp column (or row version or whatever), is internal control data, and should not carry any value to a business user.

It is/was used with the function TSEQUALS() to determined if a row had changed between read and update. A short cut to adding all the columns to the where clause.