I just found the problem - there was a duplicate row and the select query was failing - I just needed to look far enough down the list to find the error.

Thanks anyway.


On 11/05/2010, at 12:16 AM, Duncan Groenewald wrote:

The following query fails complaining about the subquery returning multiple records even though the select query works fine on its own.

insert into TableA (ID, ParentID, IndexNo, Type, Description, ProjectCode)
select A1.NR,
(select A2.NR from TableB A2
where A2.WBSCODE = A1.PARENTID),
IndexNo, Type, Title, 'AAA02'
from TABLEB A1


SELECT query runs fine on its own

select A1.NR,
(select A2.NR from TableB A2
where A2.WBSCODE = A1.PARENTID),
IndexNo, Type, Title, 'AAA02'
from TABLEB A1

Any suggestions ?

I have also tried using a VIEW which also works fine if its just a 'SELECT * from TABLEB'.  But I get the same error when trying a INSERT INTO TABLEA () SELECT * FROM TABLEB.

Thanks

Duncan





Duncan Groenewald
mobile: +61406291205