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
	OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY;

Open in new window

Southern_GentlemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
pcelbaCommented:
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/
Southern_GentlemanAuthor Commented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
pcelbaCommented:
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.
Southern_GentlemanAuthor Commented:
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.

linkedserver
pcelbaCommented:
OK, now I understand you would like to use T-SQL MERGE command to sync your SQL Server data with SQLite database.

But you still did not specify missing info. How many rows are we talking about? You should also specify whether you need to insert missing rows or also update existing rows. And what about deletions?

This will most likely mean to read the whole SQLite table so it is irrelevant whether you use OPENQUERY or Linked Server four parts query. The OPENQUERY will read the whole table just once. Four parts query may cause multiple readings from SQLite but that depends on the execution plan used.

The SQLite syntax must be in the OPENQUERY call only. Four parts query requires T-SQL syntax.

Timestamp in SQLite table would reduce the amount of data necessary to transfer to SQL Server for synchronization. If you don't have any timestamp but you are able to identify what data were changed then it could also be OK.

What I would do is: Use OPENQUERY to read all (or changed) data from SQLite database to SQL Server temporary table and then process this temp table in SQL Server.

If you were looking for other methods of data syncing then you may look at various 3rd party tools, e.g. SyncStudio (https://www.dbsyncstudio.com/challenges-of-mobile-db-sync/) or DBSync (https://dbconvert.com/sqlite/mssql/).

Of course, you could write your own tool in e.g. C# but that's outside of this question scope probably.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Southern_GentlemanAuthor Commented:
I went with a tool to sync my linked server to my sql server database tables.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.