代码之家 › 专栏 › 技术社区 › Moudiz

如何复制时间戳数据类型

sybase-ase sybase

0

Moudiz · 技术社区 · 6 年前

我有一张桌子和一张桌子 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

我得到这个警告:

警告:不能将非空值插入到 时间戳 列。数据库时间戳值已插入到而是字段。

时间戳

我的问题
如何用 时间戳 列,同时保留 时间戳 源表中的值?

(是否有类似的设置 SET IDENTITY ON/OFF )

我的设想
我有两个表,一个用于“实时”数据,另一个用于“备份”,所以我需要用完整的。我需要它完好无损,以便检测“live”行是否发生了更改。

1 回复 | 直到 6 年前

1

3

markp-fuso 6 年前

Sybase(现在的SAP)复制服务器(SRS)可以在Sybase/SAP ASE表之间复制时间戳值,也就是说,SRS maintuser可以将显式值插入类型为的列中 timestamp

这怎么可能?有几个要求:

执行插入(到 replication_role 角色(必须是活动的)
set timestamp_insert on 命令(注意:如果用户没有 复制\u角色 )
您需要在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

在ASE15.7SP138数据服务器上进行了测试。