I have a scheduled AM process on SQL Server 2008 that imports data from Oracle using a linked server. I am overwriting the imported data from Oracle using drop table, then select into pattern
Apparently, the presence of the "order by" affects my end result! Take a look.
--This works fine to give me the one row I'm expecting from the newly imported table:
drop table t1;
SELECT * into t1 fROM OPENQUERY(ODBC_CSRPT,'
select
EXTERNAL_ORGANIZATION_ID
,ORGANIZATION_DESC
,STATE
from sysadm.uv_CS_EXTERNAL_ORGANIZATIONS
order by EXTERNAL_ORGANIZATION_ID asc ');
go
select * from t1
where external_organization_id = '1000107'
go
But this (below) returns no rows. My orgId of 1000107 is now missing?
drop table t1;
SELECT * into t1 fROM OPENQUERY(ODBC_CSRPT,'
select
EXTERNAL_ORGANIZATION_ID
,ORGANIZATION_DESC
,STATE
from sysadm.uv_CS_EXTERNAL_ORGANIZATIONS ');
go
select * from t1
where external_organization_id = '1000107'
go
As you can see the only thing changed is the presence of the order by clause. Another tidbit is that the linked server query is returning same rowcount (51,225 rows to be exact) whether or not the "order by" exists. Any ideas?