I need to duplicate rows in a table with unique index using sql.
I try to run this statement:

insert into
MINUY_MADRICHIM(TAARICH_MINUY_ME,TAARICH_MINUY_AD, SUG_TEUDA,
ZEHUT_MORE,TAARICH_HAFAKAT_MINUY,MAKOR_TAKZIV,SAL_ HADRACHA,MINHAL,
YECHIDA,TCHUM,ZEHUT_MEFAKEACH,STATUS_MINUY_HADRACH A,YOM_HADRACHA,
SEVEV_HADRACHA,MOSAD_HADRACHA,SHAOT_HADRACHA,BASIS _MISRA,
PITZUL_MISRA,TAA_IDKUN,SUG_MAKOR_MEDA,MAKOR_MEDA,M ACHOZ_MEADKEN,
MACHOZ_MAAVIR,TAARICH_HAAVARA_ME_MACHOZ,TAHALICH_M AAVIR)
values ('1-sep-2004', '31-aug-2005',(select SUG_TEUDA,ZEHUT_MORE,
TAARICH_HAFAKAT_MINUY,MAKOR_TAKZIV,SAL_HADRACHA,MI NHAL,YECHIDA,
TCHUM,ZEHUT_MEFAKEACH,STATUS_MINUY_HADRACHA,YOM_HA DRACHA,
SEVEV_HADRACHA,MOSAD_HADRACHA,SHAOT_HADRACHA,BASIS _MISRA,
PITZUL_MISRA,TAA_IDKUN,SUG_MAKOR_MEDA ,MAKOR_MEDA,MACHOZ_MEADKEN,
MACHOZ_MAAVIR,TAARICH_HAAVARA_ME_MACHOZ,TAHALICH_M AAVIR from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'));

And I get this error message:

%SQL-F-SUBTOOVAL, Column select expression specifies too many columns.

Did any one have an idea how can I fix this problem?

Thanks

<beersh (AT) gmail (DOT) com> wrote

The problem is the nested SELECT statement: It does not return an atomic
list value
for the third argument of the INSERT statement's VALUES list.

The nested SELECT returns a ROW of values as the third argument,
when only one atomic value can be accepted.

Here's a possible - though ugly - fix:

SQL> insert into
MINUY_MADRICHIM
(TAARICH_MINUY_ME,
TAARICH_MINUY_AD,
SUG_TEUDA,
ZEHUT_MORE,
TAARICH_HAFAKAT_MINUY,
MAKOR_TAKZIV,
SAL_HADRACHA,
MINHAL,
YECHIDA,
TCHUM,
ZEHUT_MEFAKEACH,
STATUS_MINUY_HADRACHA,
YOM_HADRACHA,
SEVEV_HADRACHA,
MOSAD_HADRACHA,
SHAOT_HADRACHA,
BASIS_MISRA,
PITZUL_MISRA,
TAA_IDKUN,
SUG_MAKOR_MEDA,
MAKOR_MEDA,
MACHOZ_MEADKEN,
MACHOZ_MAAVIR,
TAARICH_HAAVARA_ME_MACHOZ,
TAHALICH_MAAVIR)
values
('1-sep-2004',
'31-aug-2005',
(select SUG_TEUDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select ZEHUT_MORE from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAARICH_HAFAKAT_MINUY from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MAKOR_TAKZIV from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SAL_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MINHAL from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select YECHIDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TCHUM from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select ZEHUT_MEFAKEACH from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select STATUS_MINUY_HADRACHA
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select YOM_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SEVEV_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MOSAD_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'),
(select SHAOT_HADRACHA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select BASIS_MISRA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select PITZUL_MISRA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAA_IDKUN from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select SUG_MAKOR_MEDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MAKOR_MEDA from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MACHOZ_MEADKEN from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select MACHOZ_MAAVIR from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAARICH_HAAVARA_ME_MACHOZ
from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row),
(select TAHALICH_MAAVIR from
MINUY_MADRICHIM where TAARICH_MINUY_ME = '1-sep-2003' and
TAARICH_MINUY_AD = '31-aug-2004'
limit to 1 row)
);