Archives of the TeradataForum

Message Posted: Mon, 19 Dec 2000 @ 01:25:57 GMT

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

I generate multiple lines similar to the one below by concatenating the Text, databasename and tablename from DBC.TABLES. This generates one long line not a separated line as show below.

delete from database.testtable all;insert into database.testtable
select *
from
database.prodtable WHERE field1 IN (SELECT field1 FROM
database.sampletable);
COLLECT STATISTICS ON database.testtable;

Below is how I would like the above line to look:

delete from database.testtable all;
insert into database.testtable
select * from database.prodtable
WHERE field1 IN (SELECT field1 FROM database.sampletable);
COLLECT STATISTICS ON database.testtable;

Does anyone know how I can break up the first line to look like the second using SQL?

What I am trying to do is build a script to reload a test database with multiple tables from a production database with matching tables using a sample table to limit the size of the test database.

I know I can generate each line individually but would like to do this without a lot of extra editing to put the strings together after generating. There are over 150 tables.

I would like to do this the easy way by generating a file using BTEQ that I can then use as input to BTEQ to reload the test database. I have come up with a way to do this but it takes about 100 lines of SQL with lots of Unions within Unions. It is hard to follow the flow of the SQL. I end up creating a column that is a sort key and not something I can load into BTEQ without further manipulation.

Any Ideas?

Jamie Brunett

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