Auto code generation for data movement across environment : I/P required

Database
Enthusiast

Auto code generation for data movement across environment : I/P required

Hi All,

I know that for moving data from one environment to another environment of Teradata there is lot of methods like bteqs, nparc, arcmain etc.

But other then these I am working on interesting code / macro / procedure for generating insert command for every record of table, which can be beneficial for all developers in moving small amount of data , kind of dimensions tables across the environment.

The idea behind this effort is to minimize data movement request from different application developer teams for artchitect or DBA team, so that they can be self sufficient in case of small data movement ( till 1000 rows). Why I am saying 1000 rows because more then this will be potential candidate for other methods. Also code should not generate if row count is more then 1000.

Now let me try to explain what I am looking for:

Suppose in TDDEV I do have below table.

CREATE TABLE TDDEV.TEST1
(
COL1 INTEGER,
COL2 DECIMAL(10,0),
COL3 DECIMAL(15,5),
COL4 VARCHAR(10))
PRIMARY INDEX (COL1)

Row Count : 100

Now developer want to move this data from DEV to QA environment.

So the code should be something like this:

SELECT
'INSERT INTO QA.TEST1 VALUES(' ||
CASE WHEN TRIM(COL1) IS NULL THEN 'NULL' ELSE TRIM(COL1) END || ',' ||
CASE WHEN TRIM(COL2) IS NULL THEN 'NULL' ELSE TRIM(CAST(COL2 AS INTEGER)) END || ',' ||
CASE WHEN TRIM(COL3) IS NULL THEN 'NULL' ELSE TRIM(COL2) END || ',' ||
CASE WHEN TRIM(COL4) IS NULL THEN 'NULL' ELSE SUBSTR('''',1,1) || TRIM(COL2) || SUBSTR('''',1,1) END || ',' || ');'
FROM TDDEV.TEST1

So if you look here casting and quotes depends on data type of the columns.
And I need to define column name manually.

So I am working on a code which will take DB NAME and Table Name as i/p and hit DBC.COLUMNS will give o/p of single record something like above only if record count of the table is less then 1000.

Please let me know if any one has implemented something like this.
Or if you have any idea on similar lines.

Note : I want to implement this code in Teradata (not on unix server) itself due to easy access and maintenance.

thnx for looking at post.

Regards,
Subhash

1 REPLY
Enthusiast

Re: Auto code generation for data movement across environment : I/P required

Hi, Your approach looks good. But we are currently facing issues with some business users who are writing insert queries for each record and submitting at once. When they submit more inserts like these at once the target table will be locked for write separately for each query and its blocking the other users. Where as in traditional methods like nparc, tpt, etc. we have an advantage of having insert as one request for whole data transfer.