Re: HELP with "INSERT SELECT"

[SGreen]

I have two suggestions:
First - drop those parentheses when doing a single INSERT ... SELECT ... 
(http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html)

INSERT fun_informations
SELECT conn.INT_InternalCallId, 
        'CID', 
        'AAAAAA', 
        1, 
        null 
FROM cti_connections conn 
LEFT JOIN app_ivrtable ivr 
        ON conn.chr_device=ivr.chr_device 
WHERE conn.int_state = 7 
        and ivr.CHR_Machine = 'm1' 
        and ivr.INT_IVRLine = '1'

Second - try a UNION to chain your queries together. 
(http://dev.mysql.com/doc/mysql/en/UNION.html)

INSERT fun_informations
SELECT conn.INT_InternalCallId, 
        'CID', 
        'AAAAAA', 
        1, 
        null 
FROM cti_connections conn 
LEFT JOIN app_ivrtable ivr 
        on conn.chr_device=ivr.chr_device 
WHERE conn.int_state = 7 
        and ivr.CHR_Machine = 'm1' 
        and ivr.INT_IVRLine = '1'
UNION
SELECT conn.INT_InternalCallId, 
        'CPN', 
        'BBBBB', 
        1, 
        null 
FROM cti_connections conn 
LEFT JOIN app_ivrtable ivr 
        ON conn.chr_device=ivr.chr_device 
WHERE conn.int_state = 7 
        and ivr.CHR_Machine = 'm1' 
        and ivr.INT_IVRLine = '1'

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Daniel Bronzeri Barbosa" <daniel@stripped> wrote on 09/22/2004 
08:01:59 AM:

> How I can Insert 2 records with a Insert Select?
> 
> The SQl Clause that I am using is this:
> 
> Insert into fun_informations
> (Select conn.INT_InternalCallId as INT_InternalCallId, 
>  'CID' as CHR_PropName, 
>  'AAAAAA' as CHR_PropValue, 
>  1 as INT_Updated, 
>  null as INT_Key 
>  from cti_connections conn left join app_ivrtable ivr 
>  on conn.chr_device=ivr.chr_device 
>  where conn.int_state = 7 and 
>  ivr.CHR_Machine = 'm1' 
>  and ivr.INT_IVRLine = '1')
> 
> but I wanted to insert given of this clause
> 
> Insert into fun_informations
> (Select conn.INT_InternalCallId as INT_InternalCallId, 
>  'CID' as CHR_PropName, 
>  'AAAAAA' as CHR_PropValue, 
>  1 as INT_Updated, 
>  null as INT_Key 
>  from cti_connections conn left join app_ivrtable ivr 
>  on conn.chr_device=ivr.chr_device 
>  where conn.int_state = 7 and 
>  ivr.CHR_Machine = 'm1' 
>  and ivr.INT_IVRLine = '1'),
> (Select conn.INT_InternalCallId as INT_InternalCallId, 
>  'CPN' as CHR_PropName, 
>  'BBBBB' as CHR_PropValue, 
>  1 as INT_Updated, 
>  null as INT_Key 
>  from cti_connections conn left join app_ivrtable ivr 
>  on conn.chr_device=ivr.chr_device 
>  where conn.int_state = 7 and 
>  ivr.CHR_Machine = 'm1' 
>  and ivr.INT_IVRLine = '1')
> 
> 
>  It is possible? If not how i can do this?
> 
> Tks,
> 
> Daniel