Archives of the TeradataForum

Message Posted: Wed, 29 Oct 2002 @ 22:23:12 GMT

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

Here is a quick way to create records in a table. Your ability to vary the other columns is somewhat limited, but with a bit of imagination you could do all sorts of things to generate values for additional columns.

I am not sure I properly understand your requirement from your description of the tables, but nevertheless, you may be able to use this technique. At the end of the day, each insert doubles the number of rows in the table. To get 1000 rows, you need to run 11 inserts to generate 1,000,000 records, you need to run 21 inserts.

    create volatile table yourdata (id integer, data_value varchar (20))
on commit preserve rows;

    insert into  yourdata values (1, 'Rec 1');
    insert into yourdata (id, data_value) select id + 1 as new_id, 'Rec' || (new_id (format 'zzz9')) from yourdata;
    insert into yourdata (id, data_value) select id + 2 as new_id, 'Rec' || (new_id (format 'zzz9')) from yourdata;
    insert into yourdata (id, data_value) select id + 4 as new_id, 'Rec' || (new_id (format 'zzz9')) from yourdata;

Note that the value added to the ID doubles each time the query is run. The above sequence will have generated 8 records with ID values ranging from 1 to 8 inclusive.

The above queries, can easily be generated within a loop in any procedural language. I will leave it to you to convert this to a stored procedure. Following is an example of how we have done this in a perl script - hopefully this will help you get started converting it to a SP. I've annotated the perl script for you but it should be pretty much self explanatory.

I hope this helps.

Glenn Mc

# Run bteq and connect to the perl script.  This allows us to submit commands to bteq from this
# perl program just as if we were typing them in directly to bteq.

open (TOBTEQ, $bteq_cmd)|| die "\n $err_fopen bteq command";

# This print statement sends a block of text to bteq.
# The block is delimited by the EOI_Startup Lable
print TOBTEQ << "EOI_Startup";
.SET SESSION CHARSET $charset
$logon_string
DATABASE $vector_db;

Delete from Calendar_Date
All;

-- Seed the Calendar_Date table with an initial record;
## This is equivelent to the first insert in the example above. It is
## needed to get the first record into the table.  Without this record
## the insert select will have nothing to work with.

Insert into Calendar_Date (calendar_id, target_date, sequence)
Select  0, process_date, 0
From    Run_Date;

.IF ERRORCODE != 0 THEN .GOTO ERROR_FOUND
.IF ACTIVITYCOUNT != 1 THEN .GOTO ERROR_FOUND

EOI_Startup
#Now we are back to perl script

# This loop generates the necessary insert selects which are sent to bteq.
# The index of the loop ($i) corresponds to the id increment in the above example.
# Note that the index is doubled ($i *= 2) rather than incremented (I = I + 1).
# It stops when the index exceeds the calendar size.
for ($i = 1; $i < $calendar_size; $i *= 2) {
        print TOBTEQ << "EOI_LoadDates";

                Insert  into Calendar_Date (calendar_id, target_date, sequence)
                Select  calendar_id, target_date+$i, sequence+$i
                From    Calendar_Date;
                .IF ERRORCODE != 0 THEN .GOTO ERROR_FOUND
EOI_LoadDates
}

# Finally, send the following bteq commands to bteq.
# The delete is required to get the exact number of rows requested by Calendar_Size.

print TOBTEQ << "EOI_Finishup";
Delete  from Calendar_Date
Where   sequence > $calendar_size;

.IF ERRORCODE != 0 THEN .GOTO ERROR_FOUND

.LABEL NO_CAL_CALCULATION
.QUIT 0;

.LABEL ERROR_FOUND
.QUIT 999;

EOI_Finishup
close (TOBTEQ);

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