Archives of the TeradataForum

Message Posted: Thu, 08 Jun 2006 @ 13:26:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You might also consider building a solution that uses the DBC Catalog and the power of Teradata to create the insert scripts for you, since there may be other tables and requirements to perform these types of table conversion. Please understand that the majority of the work I do is with Teradata using batch mainframe jobs, so the following may have to be tweaked depending on how you access Teradata.

Job step one - create a control member to read the DBC.TABLES catalog table to generate the "mass" of insert statements based on the table into another control member:

     .EXPORT REPORT DDNAME=CTL001;
     .FORMAT OFF;
     .SET WIDTH 72;
     .SET FOLDLINE ON ALL;

     SELECT 'INSERT INTO [New DBS].[New TBL]'                    (TITLE'');
     SELECT ';SELECT CUSTOMER_NUMBER'                            (TITLE'')
          , '      , ' || CSUM(1,A.COLUMNNAME) || ' AS COL_TYPE' (TITLE'')

          , '      , ' ||A.COLUMNNAME || ' AS COL_DATA'          (TITLE'')

          , '   FROM [OLD DBS].[OLD TBL]'                        (TITLE'')
          , '  WHERE ' || TRIM(A.COLUMNNAME) || ' IS NOT NULL'   (TITLE'')
       FROM DBC.COLUMNS A
      WHERE DATABASENAME = 'ACPD0848'
          AND TABLENAME = 'JBH_EMPLOYEE_MANUAL2'

       ORDER BY COLUMNNAME
     ;
     SELECT ';'                                                  (TITLE'');

     .EXPORT RESET;

NOTE: If the last positions of the old tables column name is the value that needs to be inserted for COL_TYPE, then replace the CSUM function with something like SUBSTR(A.COLUMNNAME,4,3).

Job step two - execute BTQMAIN using the created control card from job step one - assuming you've already created the new database and table to be insert to.

Rinse and repeat for each table to be converted.

Thanks,

John

	<Prev	Next>		<<First	<Prev	Next>	Last>>