Hey all,
Just something I found that I thought I would share.
Don’t know if anyone else had tried this before apart from myself, but Teradata was previously not able to auto increment an integer like mysql/oracle, or for those Access kiddies auto_number. As of V2R5 though Teradata have included an auto increment function, not something you would normally need to use regularly but can be handy. Syntax is pretty simple, one caveat though, although it is auto increment doesn’t mean sequential increment so you won’t get 1,2,3,4,5,6, you might get 1, 101,1002,1003,1110 etc.. it uses it’s own method but the values are incrementing and unique.
Here is a sample table create
CREATE volatile TABLE autonum_test
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
,NO FALLBACK
(
ID_COL INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100000000 NO CYCLE)
,solution_name VARCHAR(100)
,Expected_date_time TIMESTAMP(0)
,Start_date_time TIMESTAMP(0)
,Completion_status VARCHAR(10)
,Completion_date_time TIMESTAMP(0)
)
UNIQUE PRIMARY INDEX ( ID_COL );INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_1′,’2009-01-02 00:00:00′,’2009-01-02 00:00:00’,’complete ‘,’2009-01-02 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_2′,’2009-01-03 00:00:00′,’2009-01-03 00:00:00’,’started ‘,’2009-01-03 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_3′,’2009-01-04 00:00:00′,’2009-01-04 00:00:00′,’ ‘,’2009-01-04 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_4′,’2009-01-05 00:00:00′,’2009-01-05 00:00:00’,’complete ‘,’2009-01-05 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_5′,’2009-01-06 00:00:00′,’2009-01-06 00:00:00’,’started ‘,’2009-01-06 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_6′,’2009-01-07 00:00:00′,’2009-01-07 00:00:00′,’ ‘,’2009-01-07 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_7′,’2009-01-08 00:00:00′,’2009-01-08 00:00:00’,’complete ‘,’2009-01-08 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_8′,’2009-01-09 00:00:00′,’2009-01-09 00:00:00’,’started ‘,’2009-01-09 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_9′,’2009-01-10 00:00:00′,’2009-01-10 00:00:00′,’ ‘,’2009-01-10 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_10′,’2009-01-11 00:00:00′,’2009-01-11 00:00:00’,’complete ‘,’2009-01-11 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_11′,’2009-01-12 00:00:00′,’2009-01-12 00:00:00’,’started ‘,’2009-01-12 00:00:00’;
INSERT INTO autonum_test(solution_name,Expected_date_time,Start_date_time,Completion_status,Completion_date_time) SELECT ‘item_12′,’2009-01-13 00:00:00′,’2009-01-13 00:00:00′,’ ‘,’2009-01-13 00:00:00’;select * from autonum_test;
If you run the select statement you will see what I mean about how teradata does the increments, as I mentioned not something we would use everyday, and remember there is some overhead on load, I haven’t tested fastload so not sure if supported in fastload, but could come in very handy for tables with poor unique index to avoid skew or as a versioning assist.
Anywho as I said, just something I found that I didn’t know teradata had started supporting so thought I’d share.