在 sybase ASE 中，无法使用 Proxy_table 命令插入 [英] In sybase ASE, Unable to insert using Proxy_table Command
问题描述
我使用 Sybase ASE 15.7 并且在使用默认值插入代理表时遇到问题.我们希望将数据从源表迁移到目标表，其中目标表在远程服务器中具有非空和默认值的附加列.请查看下表详细信息.
源表:
CREATE TABLE TABLE_SOURCE(COL1 INT,COL2 INT,COL3 INT)目标表:
CREATE TABLE TABLE_TARGET(COL1 INT,COL2 INT,COL3 INT,COL4 INT 默认值 0 非空)在源数据库中创建代理表:
CREATE Proxy_Table TAB_TARGET AT 'TGT_SERVER.DB.DBO.TABLE_TARGET'在 Source 中，我们有三列的详细信息，我们用它构建了 Query.
脚本:
INSERT INTO TAB_TARGET (COL1,COL2,COL3) 值 (1,2,3)错误:
表 TAB_TARGET 中的列 COL4 不允许空值.，错误 233，第 1 行虽然我们已经为 COL4 声明了默认的 cotraint，但它没有通过代理表接受.有什么办法可以解决这个问题.我们有超过 5000 个用户正在迁移数据的表，而这个问题存在于 100 多个表中.
有没有办法插入目标表而不会出现 NOT NULL 问题
以下内容显示在 参考手册: 命令:创建proxy_p<块引用>
"create proxy_table 不会导入列默认值和规则；用创建现有表，其中列属性可以定义."
注意:此参考来自 ASE 16 手册；我在 ASE 15.7 SP100 手册中找不到此评论；我假设在编写 ASE 15.7 手册时忽略了这个细节.
由于您要处理大量表，因此我假设您已经编写了一些脚本来加快此迁移过程(并最大限度地减少因手动创建 5K 代理表和相关的INSERT 语句).
如果这个假设是真的，并且您不希望创建 5K create existing table 命令的麻烦(完成所有那些讨厌的列详细信息)，我想知道它是否会是(相对)更容易生成具有显式默认值的 INSERT 语句，例如:
INSERT INTO TAB_TARGET (COL1,COL2,COL3,COL4) 值 (1,2,3,0)至于如何在系统表中找到默认值...
- 如果列具有默认值，则syscolumns 中的关联记录将在 cdefault 列中存储一个非零值，即syscolumns.cdefault >0.
- 非零 cdefault 值是指向 syscomments 中存储有关默认值信息的记录的指针，即 syscolumns.cdefault= syscomments.id.
- 实际默认值存储在 syscomments.text 列中两种格式之一，取决于默认是否为 a) 定义的内联作为 create/alter table 命令的一部分，或者 b) 通过 create default 命令定义为独立默认值.
示例:
-- 分配给列 'b' 的内联默认值 '0'创建表 t1 (a int, b int default 0 not null, c int)去-- 名为myzero"的独立默认值，默认值为0"；绑定到c"列创建默认 myzero 为 0去sp_bindefault myzero, 't1.c'去sp_help t1去...Column_name ... 默认名称----------- ... --------------一个 ... NULLb ... t1_b_467529718 -- 内联默认值c ... myzero -- 独立默认...-- syscolumns.cdefault 的内容select name, cdefault from syscolumns where id = object_id('t1') order by colid去名称 cdefault---------- -----------0b 467529718c 387529433-- syscomments.text 的内容select id,text from syscomments where id in (467529718,387529433) order by id去标识文本--------- ---------------------------467529718 DEFAULT 0 -- 内联默认值387529433 创建默认 myzero 为 0 -- 独立默认- 将所有这些放在一起:选择 col.name, com.text来自 syscolumns col，系统评论 com其中 col.id = object_id('t1')和 col.cdefault = com.id按 1 订购去名称文本---------- ------------------------------b DEFAULT 0 -- 内联默认值c create default myzero as 0 -- 独立默认从这里您应该能够根据 syscomments.text 列的格式和实际的默认值(即，它是数字吗?是字符串，可能带有嵌入空间?它是一个函数引用，例如，getdate()).
Im using Sybase ASE 15.7 and facing issue while proxy table insert with Default value. We want migrate data from source table to Target table, where target table is in remote server has Additional Columns with Not null and Default values. Please find the below Tables Details.
Source Table:
CREATE TABLE TABLE_SOURCE
(
COL1 INT,
COL2 INT,
COL3 INT
)
Target Table:
CREATE TABLE TABLE_TARGET
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT Default 0 NOT NULL
)
Creating Proxy table in Source DB:
CREATE Proxy_Table TAB_TARGET AT 'TGT_SERVER.DB.DBO.TABLE_TARGET'
In Source we have details of three columns and we framed Query with that.
Script:
INSERT INTO TAB_TARGET (COL1,COL2,COL3) values (1,2,3)
Error:
The column COL4 in table TAB_TARGET does not allow null values., Error 233, Line 1
Though we have declare default costraint for COL4 but it is not accepting that through Proxy table. Is there any way to fix this issue. We have more than 5K Tables where users are migrating data and this issue is there in more than 100 tables.
Is there any way to insert into target table without NOT NULL issue
The following shows up under the Usage section of Reference Manual: Commands: create proxy_table:
"Column defaults and rules are not imported by create proxy_table; use create existing table instead, where column properties can be defined."
NOTE: This reference is from the ASE 16 manuals; I could not find this comment in the ASE 15.7 SP100 manuals; I'm assuming this detail was overlooked when the ASE 15.7 manuals were written.
Since you're dealing with a large volume of tables I'm gong to assume you've got some scripting in place to speed up this migration process (and to minimize errors that could arise from manually creating 5K proxy tables and the associated INSERT statements).
If this assumption is true, and you don't want the hassles of creating 5K create existing table commands (complete with all of those pesky column details), I'm wondering if it would be (relatively) easier to to generate the INSERT statements with explicit default values, eg:
INSERT INTO TAB_TARGET (COL1,COL2,COL3,COL4) values (1,2,3,0)
As for how to find the default value in the system tables ...
- If a column has a default value the associated record in syscolumns will have a non-zero value stored in the cdefault column, ie, syscolumns.cdefault > 0.
- The non-zero cdefault value is a pointer to a record in syscomments where information about the default is stored, ie, syscolumns.cdefault = syscomments.id.
- The actual default value is stored in the syscomments.text column in one of two formats depending on if the default was a) defined inline as part of a create/alter table command or b) defined as a standalone default via the create default command.
Example:
-- inline default value of '0' assigned to column 'b'
create table t1 (a int, b int default 0 not null, c int)
go
-- standalone default named 'myzero' with default value of '0'; bound to column 'c'
create default myzero as 0
go
sp_bindefault myzero, 't1.c'
go
sp_help t1
go
...
Column_name ... Default_name
----------- ... --------------
a ... NULL
b ... t1_b_467529718 -- inline default
c ... myzero -- standalone default
...
-- contents of syscolumns.cdefault
select name, cdefault from syscolumns where id = object_id('t1') order by colid
go
name cdefault
---------- -----------
a 0
b 467529718
c 387529433
-- contents of syscomments.text
select id,text from syscomments where id in (467529718,387529433) order by id
go
id text
--------- ---------------------------
467529718 DEFAULT 0 -- inline default
387529433 create default myzero as 0 -- standalone default
-- pulling this all together:
select col.name, com.text
from syscolumns col,
syscomments com
where col.id = object_id('t1')
and col.cdefault = com.id
order by 1
go
name text
---------- ------------------------------
b DEFAULT 0 -- inline default
c create default myzero as 0 -- standalone default
From here you should be able to parse out the default value based on the format of the syscomments.text column and the actual default value (ie, is it numeric? is it a character string, possibly with embedded space? is it a function reference, eg, getdate()).
这篇关于在 sybase ASE 中，无法使用 Proxy_table 命令插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
