Avatar of Southern_Gentleman
Flag for United States of America asked on

Complex OpenQuery statement

I'm wondering if it possible to query statement below in openquery. What are the limitations. I'd like to query the linkedserver directly instead of creating a merged statement to sync the tables from the linkedserver to sql server table.

	select CAST(a.dtevent as date) as 'date',
	Count(*) as '#ofTrans', max(CAST(a.dtevent as datetime)) as 'lastTrans'
	From [dbo].[wdir_Weborderupload_wdevents] as a 
	GROUP BY CAST(a.dtevent as date)
	order by date desc

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

You are working with 7 rows from the linked server. That doesn't look like you should invest effort in optimizing. But that depends on what kund of synchronisation and correlation you have between the tables.
OpenQuery does not allow optimizing the remote part, it executes the complete SQL remotely and than can only work on that complete resultset.
Pavel Celba

In short:
OPENQUERY sends the complete query to the remote server so all optimizations are done on the remote server.
Commands executed as Linked Server four parts queries may vary.

The optimization in your case depends on index on [date] column existence. Does the table have such index? If yes then there should be no major difference in the speed between the two commands.

You may read more about the two kinds of query e.g. here: https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/

so is there a good way for me to sync the linked server to my SQL server database tables. I used an odbc driver to add a linked server from an SQLite database im just having trouble syncing the linked database to the SQL server database without always using a merge statement.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Without knowing a lot about the data, good advice is difficult to give. Usually you use local staging table containing interesting rows, and act on that one. It depends on what kind of sync you need, how many rows are to process and much more.
Pavel Celba

First of all you should explain what do you mean by the "merge statement", second the query in your question does not follow SQLite syntax.

If the "merge" means to create a text string which is then used in OPENQUERY call then I don't see it problematic in any way.

If you need to sync some data from SQLite to SQL Server then you would need some timestamp which you may use to retrieve just the data updated since the last synchronization. Do you have such column in your data? Or would you like to "sync" just the last 7 rows independently on the current SQL Server data contents?

Once you know what should be the correct syncing process then you may design SQL command for data reading with appropriate syntax for given environment.  To read just the 7 rows in SQLite you would need LIMIT 7 OFFSET 0.

The best way I would propose is to read all the new/changed rows (or whatever number of rows you need to transfer) from SQLite db and store them to a temporary table and process them at the SQL Server side.

From the my original question how would I write that statement in sqlite syntax? from what i'm reading in all my online articles, i guess i'll have to create a merge query to update,insert and delete to sync my linkedservers (in my sql server linked server folder) to my MS SQL Server tables. I was simply looking for advice on if there is any other method of syncing data from a linedserver to an MSSql table.  If i'm using a Merge i don't understand why i need to create a timestamp.

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pavel Celba

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

I went with a tool to sync my linked server to my sql server database tables.