Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Pasring a SQL select result
 
From:   James S. Brunett

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016