BUG: Select/Into Another Database Changes Timestamp Datatype (145907)

SYMPTOMS

When you create a table in a different database by using the SELECT/INTO statement, the timestamp datatype that exists in the source table changes to a binary (8) dataype in the target table, but new timestamp values are generated for the binary (8) column in the target table.

CAUSE

In a SELECT/INTO, the server perceives a timestamp column as a special datatype. When the server is trying to create the new table for the target database, it uses a predefined list of datatypes based on the "storage type" of the original column. In the case of timestamp columns, its storage type is 45 (found in systypes.usertype), which corresponds to a binary column.

In SQL Server 6.0, SELECT/INTO uses the base type instead of the user- defined datatype because base types are common across all databases and prevent mismatches of user-defined datatypes in systypes.usertype across databases.

WORKAROUND

If the timestamp datatype is needed for the target table, first create the table in the other database by using the CREATE TABLE statement or by using Manage Tables to define the timestamp column as datatype timestamp. Then use INSERT/INTO or BCP to selectively copy columns from the source table to the target table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.0 and 6.5. Starting with SQL Server version 7.0, the table created by SELECT INTO will retain both the timestamp data type and the values from the original table.