Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ Teradata Syntax Migration/ Schema Objects and Data Definition Language (DDL)/ Migrating Tables/ Support for Specified Columns
Updated on 2024-07-19 GMT+08:00
View PDF

Support for Specified Columns

Migration tool supports queries that specify number of columns (not all columns specified) during INSERT. This can happen when the input INSERT statement does not contain all the columns mentioned in the input CREATE statement. During migration, the columns are added with any default values specified.

  • This feature is supported if session_mode is Teradata.
  • The SELECT statement for the INSERT-INTO-SELECT must not include the following:
    • Set operators
    • MERGE, TOP with PERCENT, TOP PERCENT with TIES

Input - TABLE with all columns of CREATE are not specified in the INSERT statement

 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
;

Output:

 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
;
Parent topic: Migrating Tables