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
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
0
Comments
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