开发者

im trying make a insert into with select and value

I'm trying to make a INSERT INTO with a SELECT and values, but no works.

TABLE SOURCE:

CREATE TABLE "MICV_PRE"."TS$SEQUENCES"
  (
    "ID_NODE"     NUMBER DEFAULT 1 NOT NULL ENABLE,
    "ID_TASK"     NUMBER DEFAULT 1 NOT NULL ENABLE,
    "ID_DOCUMENT" NUMBER DEFAULT 1 NOT NULL ENABLE,
    "ID_WORD"     NUMBER DEFAULT 1 NOT NULL ENABLE,
开发者_JAVA百科    "ID_TEAM"     NUMBER DEFAULT '1' NOT NULL ENABLE)

TABLE TO MODIFY:

  CREATE TABLE TS$SEQUENCES_NEW(
     "ID_CODE" VARCHAR(255 CHAR) NOT NULL ENABLE,
     "CODE_SUBSEQUENCE" VARCHAR2(255 CHAR) NOT NULL ENABLE,
     "VALUE"  NUMBER(10,0) NOT NULL ENABLE
  );

table source:

id_task | id_node | id_word 
  10    |    20   | 30

table to modify:

 id_code  | code_subsequence | value 
"id_task" | "empty"          | 10 
"id_node" | "empty"          | 20 
"id_word" | "empty"          | 30 


So, the SQL you tried is this:

SQL> INSERT INTO TS$SEQUENCES_NEW
  2  SELECT TS$SEQUENCES.ID_TASK AS "VALUE", 'ID_TASK' AS "ID_CODE", 'VACIO' AS "CODE_SUBSEQUENCE"
  3  FROM TS$SEQUENCES
  4  /
                                                                *
ERROR at line 2:
ORA-01722: invalid number


SQL> 

This fails because the datatypes in the projection of the query don't match the order of the columns in the table. So either change the SELECT statement or define the order in the INSERT clause:

SQL> INSERT INTO TS$SEQUENCES_NEW  ("VALUE", "ID_CODE","CODE_SUBSEQUENCE" )
  2  SELECT TS$SEQUENCES.ID_TASK AS "VALUE", 'ID_TASK' AS "ID_CODE", 'VACIO' AS "CODE_SUBSEQUENCE"
  3  FROM TS$SEQUENCES
  4  /

1 row created.

SQL>


Try this:

INSERT INTO TS$SEQUENCES_NEW (VALUE, ID_CODE, CODE_SUBSEQUENCE)
SELECT TS$SEQUENCES.ID_TASK AS "VALUE", 'ID_TASK' AS "ID_CODE", 'VACIO' AS "CODE_SUBSEQUENCE" 
FROM TS$SEQUENCES;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜