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

Wils_Gomes

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

[Deleted User]

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