Help: SQL remote view compare to local table

Hello, having trouble with a solution to only query new records in a remote
view and would like some help.

Have a local table (free vfp table) with an ID field and a remote view to
SQL table with the same ID field. What I want to do is change the remote
view query to the SQL table to pull only records where the ID's do not match
the local table. The reason is that this particular table contains 500,000
records and I'm trying to reduce the amount of data being pulled in the
remote view itself. The result set would then contain only new records in
the remote view since my last pull. No consideration needs to be made for
changed records, just new records in the remote view as determined by an ID
that does not match the local free table.

I'm was thinking something like, use the remote view, use the table then a
compare loop but that defeats the purpose as I'm still pulling all records
in the remote view.

Using the CREAT SQL VIEW and within the AS SELECT portion, can I reference
data from the remote connection AND a local table at the same time? But if
the local table isn't part of the remote connection won't that mess up? So
the SELECT would be something like CREATE SQL VIEW 'MyView' REMOTE
CONNECTION 'MyConn' AS SELECT RemoteViewTable.Field from RemoteViewTable
where RemoteViewTable.Field = LocalFreeTable.Field?

I suspect my approach is off....any ideas?

TYIA

=Ben

Anders
Tue Sep 18 15:06:43 PDT 2007

If you have an autoincrementing IDENTITY type column in the remote table and
keep track of what you checked last time you can query for values start from
there. Or a datetime column and keep track of when you checked last time.
Or a Boolean column that's null or False by default and you set it to True
when you've checked it.
-Anders

"Ben" <nospam@thankyou.com> wrote in message
news:46efefb3$0$26342$4c368faf@roadrunner.com...
> Hello, having trouble with a solution to only query new records in a
> remote view and would like some help.
>
> TYIA
>
> =Ben
>
>
>

or False

Ben
Tue Sep 18 18:44:12 PDT 2007

Thanks for the reply but I'm not sure how this helps. The question was [see
prev thread] how to query new records from the SQL remote data source
comparing to local table where ID's don't match. Unfortunately I don't have
the ability to modify the SQL database (or I wouldn't have posted the
question =)

Thanks for the response, still needing an assist though.

=Ben



"Anders Altberg" <anders.altberg> wrote in message
news:OPRZxAk%23HHA.748@TK2MSFTNGP04.phx.gbl...
> If you have an autoincrementing IDENTITY type column in the remote table
> and
> keep track of what you checked last time you can query for values start
> from
> there. Or a datetime column and keep track of when you checked last time.
> Or a Boolean column that's null or False by default and you set it to True
> when you've checked it.
> -Anders
>
> "Ben" <nospam@thankyou.com> wrote in message
> news:46efefb3$0$26342$4c368faf@roadrunner.com...
>> Hello, having trouble with a solution to only query new records in a
>> remote view and would like some help.
>>
>> TYIA
>>
>> =Ben
>>
>>
>>
>
> or False
>

Anders
Wed Sep 19 05:29:12 PDT 2007

Hi Ben
You could check out 'heterogeneous data sources' and 'distributed queries'
in BOL and see if your version of SQL Server supports them.
You could create a temporary table "CREATE TABLE #Temp (id Int )" in SQL
Server4 and load this temp table from you VFP table.
Maybe a BULK INSERT would be the quickest. Then run "SELECT * FROM Table
WHERE id NOT IN (SELECT id FROM #Temp"
You could build a string of the Id values in you VFP table and put the
string in a query:
"SELECT * FROM Table WHERE id NOT IN (string)"

-Anders

"Ben" <nospam@thankyou.com> wrote in message
news:46f07eea$0$9631$4c368faf@roadrunner.com...
> Thanks for the reply but I'm not sure how this helps. The question was
> [see prev thread] how to query new records from the SQL remote data
> source comparing to local table where ID's don't match. Unfortunately I
> don't have the ability to modify the SQL database (or I wouldn't have
> posted the question =)
>
> Thanks for the response, still needing an assist though.
>
> =Ben
>
>
>

Ben
Wed Sep 19 06:22:50 PDT 2007

Great ideas, I think the third option might be the easiest to implement.
Hadn't even considered that, THANKS!

=Ben

"Anders Altberg" <anders.altberg> wrote in message
news:eDjJ7ir%23HHA.4584@TK2MSFTNGP03.phx.gbl...
> Hi Ben
> You could check out 'heterogeneous data sources' and 'distributed queries'
> in BOL and see if your version of SQL Server supports them.
> You could create a temporary table "CREATE TABLE #Temp (id Int )" in SQL
> Server4 and load this temp table from you VFP table.
> Maybe a BULK INSERT would be the quickest. Then run "SELECT * FROM Table
> WHERE id NOT IN (SELECT id FROM #Temp"
> You could build a string of the Id values in you VFP table and put the
> string in a query:
> "SELECT * FROM Table WHERE id NOT IN (string)"
>
> -Anders
>
> "Ben" <nospam@thankyou.com> wrote in message
> news:46f07eea$0$9631$4c368faf@roadrunner.com...
>> Thanks for the reply but I'm not sure how this helps. The question was
>> [see prev thread] how to query new records from the SQL remote data
>> source comparing to local table where ID's don't match. Unfortunately I
>> don't have the ability to modify the SQL database (or I wouldn't have
>> posted the question =)
>>
>> Thanks for the response, still needing an assist though.
>>
>> =Ben
>>
>>
>>
>
>