Question:
I work with a Paradox and a MSQL database and I need to transfer data from the Paradox to a temporary table with a scope (create table #Table_name). I tried to use a batch move, but the table component didn’t find the temporary table. It only finds it if I use a temporary table with a global scope (create table ##table_name). I tried this query:
( INSERT INTO ":MSQL:#table_name" (atributes) SELECT atributes FROM (paradox_tables) )
However, the same problem occurred as did with the table component; it only accepted it with a global scope(##).
The only way I got the transfer to work is with two queries. In the first query, I used a select, and after I used an insert line by line with the result of the select. However, the performance is four times slower than the insert select in one query.
Answer:
Scopes are native to SQL Server, so the BDE can’t really use them. However, if you use pure pass-through SQL with queries you should be able to use the scope. With pure pass-through SQL, you use regular Transact-SQL with a TQuery, but instead of using Open to execute the query, you use ExecSQL instead. This means that all your processing will occur on the server. Once you have the set you need, then use a TQuery with a batch move in the normal way to transfer the data locally.
