Stored Procedure

Connectivity

Stored Procedure

Hi everyone,

I'm pretty green at this field but I have to create a table from a select statement i've made and it has to be done though a

stored procedure.

I'm wondering if someone nows any good ToDo-guides I can use. I have no experience so far regarding what the key stones are in

a stored procedure when creating a table.

Thanks in advance!

Emil

3 REPLIES
N/A

Re: Stored Procedure

Hi Emil,

there's no difference in creating a table in pure SQL or in a SP, you must follow the same rules.

I don't see any reason why you shouldn't do that without SP using a simple:

CREATE TABLE newtab AS (SELECT ......) WITH DATA PRIMARY INDEX (...);

In your SELECT you just have to add an alias to any calculated column and no ORDER BY is allowed.

You have to decide if you want a SET or MULTISET table (MULTISET might use less CPU to insert data, but might allow duplicate rows).

And most important: define a good Primary Index (if you don't specify it the PI defaults to the first column which is probably bad).

Re: Stored Procedure

Ok I see, but in order to make a SP i need to know the structure of an SP and that's where I lack in experience. I've tried to read manuals and guides but without success. I would be very glad if someone good just write down the most simpliest structure so I can take it from there. You have been very helpful and I'm amazingly satisfied so far.

N/A

Re: Stored Procedure

Hi Emil,

did you read the Stored Procdures manual? There are multiple example SPs in there.

E.g. for TD14: http://www.info.teradata.com/edownload.cfm?itemid=113480020

And if you're using SQL Assistant press F2 and change from "SQL Statements" in the top left dropdown to "Procedure Builder" to get examples for every SP statement.

In your case the simplest structure will be

CREATE PROCEDURE mySP()
BEGIN
CREATE TABLE newtab AS (SELECT ......) WITH DATA PRIMARY INDEX (...);
END;