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,
> 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.
>
> 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.
>
> This seems really difficult to do a simple insert and I was wondering if
> anyone knew if I could use something like a view that would allow me to
> insert into multiple tables with one statement? I didn't see anything
> reguarding views in the documentation.

i've found lots of views-documentation. hmm! (on my debian 2.2
system it's under /usr/share/doc/postgresql-doc/html/* ...
look under 'create rule' and 'create view' for starters.)

how about something like

create view newcust as
select
c.client_id,
c.addr_id,
c.account_type,
c.how_hear,
c.cc_type,
c.cc_exp,
c.cc_num,
c.sign_up_date,
a.addr_id
a.fname,
a.lname,
a.company,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.country,
a.phone,
a.fax,
a.email
from
customers c,
addresses a
where
c.addr_id = a.addr_id;

create rule cust_insert as
on insert to newcust
do instead
(
insert into customers
NEW.client_id,
NEW.addr_id,
NEW.account_type,
NEW.how_hear,
NEW.cc_type,
NEW.cc_exp,
NEW.cc_num,
NEW.sign_up_date
;
insert into addresses
NEW.addr_id
NEW.fname,
NEW.lname,
NEW.company,
NEW.addr1,
NEW.addr2,
NEW.city,
NEW.state,
NEW.zip,
NEW.country,
NEW.phone,
NEW.fax,
NEW.email
;
);

BUT -- if this is a 1:1 relation, and customers ALWAYS have
addresses, and addresses ALWAYS have customers, then just make
the sucker into one table and you're done.

--
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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!