The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

How to push IDs (AUTO_INCREMENT) from another DB into Vertica?

Hi,

Right now I need to let Vertica insert new IDs (auto_increment) and then remap my tables just because I can not insert the existent IDs from another DB (let's say MySQL).

but! 
I would like to be able to do something like this:

create table test( id int, txt varchar(3), primary key (id) ); 
INSERT INTO test(id, txt) VALUES(1, 'foo');
INSERT INTO test(id, txt) VALUES(2, 'bar');
INSERT INTO test(id, txt) VALUES(3, 'lol');
alter table test alter column id AUTO_INCREMENT;


How can I export data from my old Database (not Vertica) preserving the IDs?

thank you
Wils

Comments

  • Hi, 

    You can create a sequence starting from the last ID inserted and start use it when doing the insert.

    For example,

    dbadmin=> create sequence my_seq START 100;

    CREATE SEQUENCE


    dbadmin=> INSERT INTO test(id, txt) VALUES(nextval('my_seq'), 'foo');

     OUTPUT 

    --------

          1

    (1 row)

    dbadmin=> select * from test;

     id  | txt 

    -----+-----

       1 | foo

     100 | foo

       2 | bar

       3 | lol

    (4 rows)


    Hope this helps, 


    Eugenia


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.