Multiple row insert only inserting one row?

I can't seem to find any place that a similar issue has been
encountered, so here goes:
INSERT INTO destination(dest_id)
SELECT src_id
FROM source
WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
The subquery executes as expected when run by itself, returning [n]
rows. But when I try to execute the insert statement, only one row gets
inserted. Or rather, it inserts one record, then runs the SELECT
statement almost as if it was entirely separate:
(1 row(s) affected)
([n - 1] row(s) affected)
I should note that originally I was trying to insert several fields from
the source table, but was experiencing the same behavior -- only one row
getting inserted -- but some of the fields weren't getting inserted
properly either. So I pared it down to trying to just get the ID's
inserted, after which I planned on running an UPDATE.
Any advice on what I might be missing would be appreciated.Also, I tried the following to no avail:
* wrapping the SELECT statement in parentheses
* adding TOP 100 PERCENT to the SELECT
* changing the NOT EXISTS to a NOT IN () statement
Matthew Harward wrote:
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Try using a table alias...
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
> I can't seem to find any place that a similar issue has been
> encountered, so here goes:
> INSERT INTO destination(dest_id)
> SELECT src_id
> FROM source
> WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id)
> The subquery executes as expected when run by itself, returning [n]
> rows. But when I try to execute the insert statement, only one row gets
> inserted. Or rather, it inserts one record, then runs the SELECT
> statement almost as if it was entirely separate:
> (1 row(s) affected)
> ([n - 1] row(s) affected)
> I should note that originally I was trying to insert several fields from
> the source table, but was experiencing the same behavior -- only one row
> getting inserted -- but some of the fields weren't getting inserted
> properly either. So I pared it down to trying to just get the ID's
> inserted, after which I planned on running an UPDATE.
> Any advice on what I might be missing would be appreciated.
>|||Thanks for the quick reply. I tried both aliasing and using full
resolution - both with no luck:
INSERT INTO destination(dest_id)
SELECT src.src_id
FROM source src
WHERE NOT EXISTS
(SELECT des.dest_id
FROM destination des
WHERE des.dest_id = src.src_id)
and
INSERT INTO destination(dest_id)
SELECT source.src_id
FROM source
WHERE NOT EXISTS
(SELECT destination.dest_id
FROM destination
WHERE destination.dest_id = source.src_id)
Jim Underwood wrote:
> Try using a table alias...
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
>
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:bNu1g.22056$DR1.8455@.fe27.usenetserver.com...
>|||Post DDL and soem sample data and I will try to duplicate the issue. It
looks to me like it should be working. You might try putting paranthesis
around your select.
"Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> Thanks for the quick reply. I tried both aliasing and using full
> resolution - both with no luck:
> INSERT INTO destination(dest_id)
> SELECT src.src_id
> FROM source src
> WHERE NOT EXISTS
> (SELECT des.dest_id
> FROM destination des
> WHERE des.dest_id = src.src_id)
> and
> INSERT INTO destination(dest_id)
> SELECT source.src_id
> FROM source
> WHERE NOT EXISTS
> (SELECT destination.dest_id
> FROM destination
> WHERE destination.dest_id = source.src_id)
> Jim Underwood wrote:
src_id)
from
row
>|||Actually, I figured it out ... there was a INSTEAD OF INSERT trigger
messing things up.
<sigh> Triggers always seem to be the last place I look, you'd think I'd
learn my lesson by now. ;)
Thanks again for the willingness to help.
Jim Underwood wrote:

> Post DDL and soem sample data and I will try to duplicate the issue. It
> looks to me like it should be working. You might try putting paranthesis
> around your select.
> "Matthew Harward" <mharward@.alliedtradegroup.com> wrote in message
> news:Y3v1g.109156$gj5.69035@.fe16.usenetserver.com...
> src_id)
> from
> row
>