How to Copy a Timestamp Datatype

Question

I have a table with a TIMESTAMP column:

create  table dbo.EX_EMPLOYEE (
    NAME_X            varchar(10) null,
    RECORD_TIME_STAMP timestamp   null
    )

when I copy the rows from one table to another, using either:

SELECT * INTO EX_EMPLOYEE_T 
    FROM EX_EMPLOYEE 
    WHERE 1=0

or:

INSERT INTO EX_EMPLOYEE_T
    SELECT * 
        FROM EX_EMPLOYEE

I get this warning:

Warning: A non-null value cannot be inserted into a TIMESTAMP column by the user. The database timestamp value has been inserted into the TIMESTAMP field instead.

The TIMESTAMP column in the target table is replaced with the current database timestamp.

My question
How to copy rows with a TIMESTAMP column, while preserving the TIMESTAMP value from the source table ?

(Is there a setting similar to SET IDENTITY ON/OFF)

My scenario
I have 2 tables, one for "live" data and the other for "backup", so I need to copy the rows with the TIMESTAMP intact. I need it intact in order to detect if there has been a change to the "live" row.

Answer 1

The Sybase (now SAP) replication server (SRS) can replicate timestamp values between Sybase/SAP ASE tables, ie, the SRS maintuser can insert explicit values into a column of type timestamp.

How is this possible? There are a few requirements:

• the user performing the insert (to the timestamp column) must have the replication_role role (and it has to be active)
• you need to issue the set timestamp_insert on command (NOTE: this will generate an error if your user doesn't have replication_role)
• you need to explicitly list the target table's columns in the insert statement

Setup:

exec sp_displaylogin
go
...
Configured Authorization:
        ...
        replication_role (default ON)      <<<=== verify role assigned and active
        ...

create table EX_EMPLOYEE
(NAME_X                 varchar(10)     NULL
,RECORD_TIME_STAMP      timestamp       NULL
)
go

insert into EX_EMPLOYEE (NAME_X) values ('Larry')
insert into EX_EMPLOYEE (NAME_X) values ('Mo')
insert into EX_EMPLOYEE (NAME_X) values ('Curly')
go

select * from EX_EMPLOYEE
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec4304fa
 Mo         0x00000000ec4304fd
 Curly      0x00000000ec430501

select * into EX_EMPLOYEE_T FROM EX_EMPLOYEE where 1=2
go

Now for some insert tests ...

-- haven't issued the 'set timestamp_insert on' commmand, yet

insert into EX_EMPLOYEE_T
select * from EX_EMPLOYEE
go

Warning: A non-null value cannot be inserted into a TIMESTAMP column by the user. The database timestamp value has been inserted into the TIMESTAMP field instead.

-- received the *WARNING*, ie, rows are inserted but they receive new timestamp values

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec430548       <<<=== different from what's in EX_EMPLOYEE
 Mo         0x00000000ec43054a       <<<=== different from what's in EX_EMPLOYEE
 Curly      0x00000000ec43054c       <<<=== different from what's in EX_EMPLOYEE

-- enable direct insert of timestamp values

set timestamp_insert on
go

truncate table EX_EMPLOYEE_T
go

-- w/out explicitly listing target columns ...

insert into EX_EMPLOYEE_T
select * from EX_EMPLOYEE
go

-- no warning message is generated, insert succeeds, but new timestamp values are generated

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec430555       <<<=== different from what's in EX_EMPLOYEE
 Mo         0x00000000ec430557       <<<=== different from what's in EX_EMPLOYEE
 Curly      0x00000000ec430559       <<<=== different from what's in EX_EMPLOYEE

truncate table EX_EMPLOYEE_T
go

-- this time we'll explicitly list the target table's columns ...

insert into EX_EMPLOYEE_T (NAME_X, RECORD_TIME_STAMP)
select * from EX_EMPLOYEE
go

-- and now we see the timestamp values copied from the source

select * from EX_EMPLOYEE_T
go

 NAME_X     RECORD_TIME_STAMP
 ---------- ------------------
 Larry      0x00000000ec4304fa       <<<=== same as what's in EX_EMPLOYEE
 Mo         0x00000000ec4304fd       <<<=== same as what's in EX_EMPLOYEE
 Curly      0x00000000ec430501       <<<=== same as what's in EX_EMPLOYEE

Above was tested on a ASE 15.7 SP138 dataserver.