文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 表迁移/ 支持指定部分列
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

支持指定部分列

DSC支持在执行INSERT期间指定部分列（非全部列）。当输入的INSERT语句不包含输入的CREATE语句中提到的所有列时会出现这种情况。在迁移时，会向这些列添加指定的默认值。

  • session_mode设为Teradata时支持此功能。
  • INSERT-INTO-SELECT中的SELECT语句不得包含以下内容：
    • SET操作符
    • MERGE、使用PERCENT的TOP、使用TIES的TOP PERCENT

输入：TABLE，且INSERT语句中未指定CREATE中的全部列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
CREATE
     VOLATILE TABLE
          Convert_Data3
          ,NO LOG (
               zoneno CHAR( 6 )
               ,brno CHAR( 6 )
               ,currtype CHAR( 4 )
               ,Commuteno CHAR( 4 )
               ,Subcode CHAR( 12 )
               ,accdate DATE format 'YYYY-MM-DD' NOT NULL
               ,acctime INTEGER
               ,quoteno CHAR( 1 )
               ,quotedate DATE FORMAT 'YYYY-MM-DD'
               ,lddrbaL DECIMAL( 18 ,0 ) DEFAULT 0
               ,ldcrbal DECIMAL( 18 ,0 )
               ,tddramt DECIMAL( 18 ,0 ) DEFAULT 25
               ,tdcramt DECIMAL( 18 ,0 )
               ,tddrbal DECIMAL( 18 ,2 )
               ,tdcrbal DECIMAL( 18 ,2 )
          ) PRIMARY INDEX (
               BRNO
               ,CURRTYPE
               ,SUBCODE
          )
               ON COMMIT PRESERVE ROWS
;

INSERT
     INTO
          Convert_Data3 (
               zoneno
               ,brno
               ,currtype
               ,commuteno
               ,subcode
               ,accdate
               ,acctime
               ,quoteno
               ,quotedate
               ,tddrbal
               ,tdcrbal
          ) SELECT
                    A.zoneno
                    ,A.brno
                    ,'014' currtype
                    ,'2' commuteno
                    ,A.subcode
                    ,A.Accdate
                    ,A.Acctime
                    ,'2' quoteno
                    ,B.workdate quoteDate
                    ,CAST( ( CAST( SUM ( CAST( A.tddrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DEC ( 18 ,2 ) ) AS tddrbal
                    ,CAST( ( CAST( SUM ( CAST( A.tdcrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DEC ( 18 ,2 ) ) AS tdcrbal
               FROM
                    table2 A
;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
CREATE
     LOCAL TEMPORARY TABLE
          Convert_Data3 (
               zoneno CHAR( 6 )
               ,brno CHAR( 6 )
               ,currtype CHAR( 4 )
               ,Commuteno CHAR( 4 )
               ,Subcode CHAR( 12 )
               ,accdate DATE NOT NULL
               ,acctime INTEGER
               ,quoteno CHAR( 1 )
               ,quotedate DATE
               ,lddrbaL DECIMAL( 18 ,0 ) DEFAULT 0
               ,ldcrbal DECIMAL( 18 ,0 )
               ,tddramt DECIMAL( 18 ,0 ) DEFAULT 25
               ,tdcramt DECIMAL( 18 ,0 )
               ,tddrbal DECIMAL( 18 ,2 )
               ,tdcrbal DECIMAL( 18 ,2 )
          )
               ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH (
               BRNO
               ,CURRTYPE
               ,SUBCODE
          )
;

INSERT
     INTO
          Convert_Data3 (
               lddrbaL
               ,ldcrbal
               ,tddramt
               ,tdcramt
               ,zoneno
               ,brno
               ,currtype
               ,commuteno
               ,subcode
               ,accdate
               ,acctime
               ,quoteno
               ,quotedate
               ,tddrbal
               ,tdcrbal
          ) SELECT
                    0
                    ,NULL
                    ,25
                    ,NULL
                    ,A.zoneno
                    ,A.brno
                    ,'014' currtype
                    ,'2' commuteno
                    ,A.subcode
                    ,A.Accdate
                    ,A.Acctime
                    ,'2' quoteno
                    ,B.workdate quoteDate
                    ,CAST( ( CAST( SUM ( CAST( A.tddrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DECIMAL( 18 ,2 ) ) AS tddrbal
                    ,CAST( ( CAST( SUM ( CAST( A.tdcrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DECIMAL( 18 ,2 ) ) AS tdcrbal
               FROM
                    table2 A MINUS SELECT
                              lddrbaL
                              ,ldcrbal
                              ,tddramt
                              ,tdcramt
                              ,zoneno
                              ,brno
                              ,currtype
                              ,commuteno
                              ,subcode
                              ,accdate
                              ,acctime
                              ,quoteno
                              ,quotedate
                              ,tddrbal
                              ,tdcrbal
                         FROM
                              CONVERT_DATA3
;
父主题： 表迁移