Informix 7.2SE
Hi, i need to transfer some data into a table with a serial as the primary key. This in turn is used as the FK in another table. Is there any way to insert data into the table and turn the serial facility off until it is complete. Reason:- I have changed the table structure and therfore cannot use load/unload, unless of course you know diffrenet. best regards Michael
Assuming that you have already populated the serial key in the source table
you can transfer data directly in using something like :
insert into foo(ser_column, col2,col3 etc ) select ser_col, col2,col3 fromsource_table
The serial value will be maintained as the serial creation is only triggered
if you insert a zero into it.
Un-populated fields will be left as null.
Hope this helps
Howard Soper
Michael Muir-Browe <Michael@nospam.truecs.force9.co.uk> wrote in message
news:okEO2.2866$54.2156@wards...
> Hi,
>
> i need to transfer some data into a table with a serial as the primary
key.
> This in turn is used as the FK in another table.
>
> Is there any way to insert data into the table and turn the serial
facility
> off until it is complete.
>
>
> Reason:-
>
> I have changed the table structure and therfore cannot use load/unload,
> unless of course you know diffrenet.
>
> best regards
>
> Michael
>
>
Howard, Thanks for the quick response, regards Michael
Here an example with two tables: "primar" y "depend".
-----------------------------------------------------------
-- Creating demo tables and inserting values
create table primar (
fkey serial not null,
fdat integer,
primary key (fkey)
);
insert into primar values (0,1);
insert into primar values (0,2);
create table depend (
dkey integer not null references primar (fkey),
ddat char(2)
);
insert into depend values (1,"A");
insert into depend values (1,"B");
insert into depend values (2,"C");
insert into depend values (2,"D");
-- Changing type of field "fkey" from "serial" to "integer"
-- NOTE: This deletes foreing keys
alter table primar modify fkey integer not null primary key;
-- Loading new values
insert into primar values (4,4);
insert into depend values (4,"E");
insert into depend values (4,"E");
-- Changing type of field "fkey" to serial
-- NOTE: Must especify el NEXT value available
alter table primar modify fkey serial (5) not null primary key;
-- Creating foreing key
alter table depend
add constraint (foreign key (dkey) references primar (fkey));
-----------------------------------------------------------
Marco
PD: Sorry for my poor english
Michael Muir-Browe <Michael@nospam.truecs.force9.co.uk> escribi' en el
mensaje de noticias okEO2.2866$54.2156@wards...
> Hi,
>
> i need to transfer some data into a table with a serial as the primary
key.
> This in turn is used as the FK in another table.
>
> Is there any way to insert data into the table and turn the serial
facility
> off until it is complete.
>
>
> Reason:-
>
> I have changed the table structure and therfore cannot use load/unload,
> unless of course you know diffrenet.
>
> best regards
>
> Michael
>
>