我有一个表TIMESTAMP列：
create table dbo.EX_EMPLOYEE (
NAME_X varchar(10) null,
RECORD_TIME_STAMP timestamp null
)
当我从一个表复制到另一个行，并使用两种：
SELECT * INTO EX_EMPLOYEE_T
FROM EX_EMPLOYEE
WHERE 1=0
要么：
INSERT INTO EX_EMPLOYEE_T
SELECT *
FROM EX_EMPLOYEE
我得到这样的警告：
警告：非空值不能被插入TIMESTAMP由用户列。 数据库时间戳值已插入到TIMESTAMP场来代替。
该TIMESTAMP目标表列将被替换为当前数据库的时间戳。
我的问题
如何使用复制行TIMESTAMP列，同时保留TIMESTAMP从源表中的值？
（有没有设置类似的SET IDENTITY ON/OFF ）
我的场景
我有2个表，一个是“活”的数据，另一个是“备份”，所以我需要的行与复制TIMESTAMP完好。 我需要它的完整，以检测是否出现了变化的“活”行。
Sybase的（现在的SAP）的复制服务器（SRS）可以复制的Sybase / SAP ASE表，即，SRS maintuser可以插入明确的值之间的时间戳值成类型的列timestamp 。
这怎么可能？ 有几个要求：
- 执行插入（向用户
timestamp列）必须具有replication_role作用（并且它必须是活性的） - 你需要发出
set timestamp_insert on命令（注：这会产生一个错误，如果您的用户没有replication_role ） - 你需要明确列出的INSERT语句目标表中的列
设定：
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
现在，对于一些插入测试...
-- 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
上面在ASE 15.7 SP138数据服务器进行测试。
