RE: SQL Question: INSERT with SELECT in VALUES section
That's how we do it as well - we create stored procedures to do this for
routine loads. Something to think about.
Rob Vorbroker
> Richard
>
> Try:
> insert into tab1> select "nnn"
> , t2.fld1
> , "mmm"
> , t2.fld3
> , t3.fld7[1,5]
> etc etc
> from tab2 t2, tab3 t3
> where .....
>
>
> Keith
>
> -> -----Original Message-----
> -> From: Richard Spitz [mailto:Richard.Spitz@ana.med.uni-muenchen.de] ->
> Sent: Tuesday, May 13, 2003 10:53 AM
> -> To: informix-list@iiug.org
> -> Subject: SQL Question: INSERT with SELECT in VALUES section
> ->
> ->
> -> Hi Informixers,
> ->
> -> I know it is possible to use a SELECT statement _instead_ of the ->
> VALUES section of an INSERT statement, e.g.
> -> INSERT INTO tab1 (field1, field2)
> -> SELECT field_a, field_b FROM tab2
> ->
> -> Is there a way to combine that with the VALUES section of the
> -> INSERT statement? I need to insert a record into a table, but
> -> this record contains a field which I need to replace via a
> -> lookup in another table. I tried the following SQL which
> -> failed:
> ->
> -> INSERT INTO tab1 (field1, field2)
> -> VALUES ("nnnn", (SELECT field_b FROM tab2 WHERE field_a
> -> = "mmmm"))
> ->
> -> Is there a way to achieve this in a single SQL statement, or do -> I
> have to retrieve the value of field_b with a SELECT INTO statement ->
> first and then do the INSERT?
> ->
> -> Engine version is IDS 7.31UD5
> ->
> -> Regards, Richard
> ->
> ->
> ->
> -> --
> -> +-------------------------------+----------------------------
> -> -----------+
> -> | Dr. med Richard Spitz | Mail:
> -> spitz@ana.med.uni-muenchen.de |
> -> | Klinik f'r Anaesthesiologie | Tel :
> -> +49-89-7095-6110 |
> -> | Klinikum der Univ. M'nchen | FAX : +49-89-7095-6420
> -> |
> -> | 81366 M'nchen, Germany | GSM : +49-172-8933578
> -> |
> -> +-------------------------------+----------------------------
> -> -----------+
> ->
>
>
> **********************************************************************************
> This message is sent in strict confidence for the addressee only. It
> may contain legally privileged information. The contents are not to be
> disclosed to anyone other than the addressee. Unauthorised recipients
> are requested to preserve this confidentiality and to advise the sender
> immediately of any error in transmission.
> This footnote also confirms that this email message has been swept for
> the presence of computer viruses, however we cannot guarantee that this
> message is free from such problems.
> **********************************************************************************