Passing parameters from a param table in teradata...

Database
Highlighted
Enthusiast

Passing parameters from a param table in teradata...

Hello There,


I am struggling to pass parameters form an existing table list-  table

 

 

List Table looks like


DATABASE_A.TABLE_A


SRC_SCHEMA | SRC_TABLE | TGT_SCHMA | TGT_TABLE


src_scma_1 | src_tbl_1 | tgt_scma_1 | tgt_tbl_1

src_scma_2 | src_tbl_2 | tgt_scma_2 | tgt_tbl_2

src_scma_3 | src_tbl_3 | tgt_scma_3 | tgt_tbl_3

src_scma_4 | src_tbl_4 | tgt_scma_4 | tgt_tbl_4

 

Can you please suggest an efficient way to read the paramater values from each columns from DATABASE_A.TABLE_A  and pass in the following commands in LOOP till all values in the list are processed....

 

INSERT INTO tgt_scma_1.tgt_tbl_1 SELECT * FROM src_scma_1.src_tbl_1;
INSERT INTO tgt_scma_2.tgt_tbl_2 SELECT * FROM src_scma_2.src_tbl_2;
INSERT INTO tgt_scma_3.tgt_tbl_3 SELECT * FROM src_scma_3.src_tbl_3;
INSERT INTO tgt_scma_4.tgt_tbl_4 SELECT * FROM src_scma_4.src_tbl_4;


Accepted Solutions
Teradata Employee

Re: Passing parameters from a param table in teradata...

I think you mean you want a stored procedure that can read this table and do the inserts.  The logic of such an SP could look like this:

for TblList as TblList cursor for
	select SRC_SCHEMA, SRC_TABLE, TGT_SCHEMA, TGT_TABLE from DATABASE_A.TABLE_A
do
	set Stmt = 'INSERT INTO ' || TblList.tgt_schema || '.' || TblList.tgt_table ||
		' SELECT * FROM ' || TblList.src_schema || '.' || TblList.src_table;
	Execute Immediate Stmt;
end for;
1 ACCEPTED SOLUTION
2 REPLIES
Enthusiast

Re: Passing parameters from a param table in teradata...

I am trying to pass these valuse in a teradata procedure ...

Teradata Employee

Re: Passing parameters from a param table in teradata...

I think you mean you want a stored procedure that can read this table and do the inserts.  The logic of such an SP could look like this:

for TblList as TblList cursor for
	select SRC_SCHEMA, SRC_TABLE, TGT_SCHEMA, TGT_TABLE from DATABASE_A.TABLE_A
do
	set Stmt = 'INSERT INTO ' || TblList.tgt_schema || '.' || TblList.tgt_table ||
		' SELECT * FROM ' || TblList.src_schema || '.' || TblList.src_table;
	Execute Immediate Stmt;
end for;