Re: Insert data into multiple tables

On Tue, Apr 17, 2001 at 05:37:31PM -0500, K Old wrote:
> Hello,
>
> I have a general question about inserting data.
>
> I have the following tables:
>
> CREATE TABLE customer (
>     client_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

is this the same as
    client_id SERIAL
?

>     addr_id INT,
>     account_type INT,
>     how_hear INT,
>     cc_type INT,
>     cc_exp VARCHAR(5),
>     cc_num VARCHAR(16),
>     sign_up_date DATE
> );
>
> CREATE TABLE addresses (
>     addr_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
>     fname VARCHAR(16),
>     lname VARCHAR(16),
>     company VARCHAR(72),
>     addr1 VARCHAR(72),
>     addr2 VARCHAR(48),
>     city VARCHAR(32),
>     state VARCHAR(3),
>     zip VARCHAR(16),
>     country VARCHAR(32),
>     phone VARCHAR(18),
>     fax VARCHAR(18),
>     email VARCHAR(74)
> );
>
> I need to insert data into both of these tables at one time.  I want to have
> the "addr_id" field in the addresses tables to be included in the insert of
> data to the customer table.

according to your table definition, addr_id is just an int, which
you apparently must supply to postgres if it's to be anything
but NULL. seems like you'd be able to use that same value for the
next insert.

i'd wrap the two inserts within a

    begin work;
    insert into addresses ....
    insert into customers ....
    commit work;

block, of course.

> My solution so far is to have 1 insert statement that inserts the
> appropriate data into the addresses table (creating a record), then have a
> select statement pull back the most recent (which is only milliseconds old)
> record from addresses and get the addr_id (it would probably be set to a
> variable) then when all other data is gathered for the customer insert the
> addr_id would be included in that insert.

if you're trying to get the value of client_id instead, if it's a
serial value, meaning it's got this neat trigger mechanism behind
the scenes to bump the counter for every insert, then you can get
your most-recently inserted serial value via

    select currval ( 'client_id_seq'::text );

(and if you do use the serial data type in defining a table, the
"\d mytable" will show how it's used to populate new instances of
that column:

 id | integer | not null default nextval('my_id_seq'::text)

so "nextval" bumps the counter for you, andonce you do that, you
can get the most recent value used via "currval()"

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!