INSERT...SELECT affects 0 rows -- sql server programming

Mark Broadbent
9/1/2004 1:46:28 PM

I dont quite know what you are doing wrong but essentially the insert select
should work as select (or at least state that you have broken a constraint
etc ...but you give no code sample to look at).
At it's most basic level, here is a code sample that inserts from one to
another. You must make sure that you use named columns in both statements so
that you are returning and inserting the desired result set.

insert into test2..table1 (datacol)
select datacol from test1..table1

Br,

Mark.

[quoted text, click to view]

Berg
9/1/2004 8:34:42 PM

There seems to be something about INSERT...SELECT that I don't know
(probably are many things).

Running a certain SELECT statement in Query Analyzer returns 3 rows.
Using the same statement as part of an INSERT...SELECT gives the message "0
rows affected" (or whatever it is in English). And, in fact, no rows are
inserted.
Add a WHERE clause so that the SELECT returns only 1 row, and I get the
expected "1 row affected", and the row is successfully inserted.
There are no triggers on the table I'm (trying to) INSERT into.
I'm using MSSQL 2000.
I'm working on a simple repro script, but it's taking me a while and if
anyone has any insight as to what might cause this kind of behaviour, I'd
love not to have to finish it.
One possibly relevant thing: the table I'm inserting into is in a different
database (on the same server) as the one I'm selecting from.

Any help much appreciated.

sql server programming : INSERT...SELECT affects 0 rows