SQL SERVER - INSERT INTO.. SELECT.. results in different no. of rows inserted than in SELECT.. itself

Question

Hi everyone, I'm executing simple insert query like below: INSERT INTO tbl_destination ... SELECT ... FROM [SomeOtherLinkedServer].tbl_source

It results in 3'000'000 rows inserted to the destination table although the SELECT returns the 9'000'000 rows. What can be the cause? I'm using SQL Servers 2005 Standard edition. The destination table and source table are located on two different SQL Serves. No error message is shown. Destination table serves just as temporary data heap - it has no prim. key, no indexes. I've checked the disk space and amount of available space for the tbl_destination - plenty of GBs. Weird.. I will greatly appreciate any suggestions!

edit: Thank You for your suggestions so far. When I ran the SELECT COUNT(*) both locally (on linked server) and when logged to linked server itself it showed 9M rows. However when I ran the actual SELECT ... on linked server:

SELECT ... FROM [SomeOtherLinkedServer].tbl_source

the result was 3M rows and no error messages. The same query executed when logged to the remote server:

SELECT ... FROM tbl_source

also returned 3M rows but this time with error message (so it looks like no all error messages are shown when executing query that uses tables on linked server):

Msg 701, Level 17, State 123, Line 1

There is insufficient system memory to run this query.

So now it looks clear that this is memory issue. Is there any way (without installing more memory on the server) to overcome this problem - insert the whole result of this query into table? thx! One more thing - the tbl_source isnt really a table, its a view. Unfortunatelly maintained by someone else so optimizing its internals is not an option (at least for now).

Answer 1

If the destination table is empty then you can delete the table and do a Select into statment.

select * into destination_table
from [otherserver].otherdb.dbo.other_table

Answer 2

may be you have inserted three times already :)

Answer 3

Have you considered partitioning the results and batching the insert?

Insert into...
Select * from ....
Where rowid between 0 and 1000000


Insert into...
Select * from ....
Where field1 between 1000000 and 2000000

...etc