Use Macro to generate Primary key while Inserting records

UDA

Use Macro to generate Primary key while Inserting records

Hi,
I want to insert some records in the database and want to use Macro to generate the primary key of the table while inserting records. I want to know if this possible in teradata.

I have a sequence table say:

SEQUENCES : SEQ_NM VARCHAR(10),
SEQ_VAL INTEGER

I have another table say
STUDENT: STUDENT_ID INTEGER,
STUDENT_NM VARCHAR(50)

I also have a macro that returns the next available sequence number. To insert the data currently I first call the macro and then use the value returned by the macro in my insert statement.

Now, I want to write a single insert statement that will use the macro to generate the ID and also insert the record in the database in one call like we can do in Oracle using Oracle sequences.nextVal.

Any help would be appreciated.

Thanks in advance
sdhanotia
2 REPLIES

Re: Use Macro to generate Primary key while Inserting records

cant you byepass the macro and directly select from the sequence table by some sub query or something like that?

what does the macro do? it simply fetches the max+1 value?
N/A

Re: Use Macro to generate Primary key while Inserting records

I do what SG recommends. I have a subquery that gets the max id from the table, then use the sum function to increment it for each row (you could also use row_number, I think).

But, that only works if there table is only updated by one process at a time. If there are other jobs that could load the table at the same time, then you need something else to avoid collisions on the PK.

I haven't found a solution using SQL. I have a stored procedure that does essentially the same thing the your macro does, except that it accepts a parameter to indicate how many values it should "burn". I take a row count of the number of records I have to insert, request the next Id, and at the same time, consume as munay ids as I need.

Example, current sequence value is 100 and I have 500 rows to insert.
I call the procedure with the value of 500 (actually, I submit the table name as well since I use one procedure and sequence table for everything).
I get back 100, and the current sequence value for the table is set to 600.

I then use the 100 as the seed for assinging new pks. This way I avoid haveing to call the SP 500 times. If another statement request a sequence, it will get 600, even if my job is still running.

James