Using EXEC() AT [Linked Server] for Insert data from local server

Question

let me first explain a little abot my setup before letting you know the issue Im dealing with:

I have two database servers: SQL 2008 and MySql. I have a procedure in SQL that first, deletes a table in MySql and then populates it with some data from the SQL itself. For both actions im using OPENQUERY. But, as you know, it doesnt know support large data. It becomes really slow eventhou for truncating the table in MySql (it was taking like 14 minutes to delete 60k rows... insane!).

So i found out i could use this syntax for truncating:

EXEC ('TRUNCATE TABLE table_name') AT LINKEDSERVER

But i havent been able to find the syntax for inserting the results of the SQL table to my MySql table thru this. It works correct when I insert static data, like this example:

EXEC ('INSERT INTO table_name (row1, row2) VALUES (value1, value2)') AT LINKEDSERVER

But I want to insert the results from a SQL, as I said, something like this:

EXEC ('INSERT INTO table_name (row1, row2) SELECT r1, r2 FROM SQL_DB.sql_table_name') AT LINKEDSERVER

I bet that i cannot do it because in that syntax, Im executing the exec in the MySql server and the SQL table doesnt exist there. So how can i referrence my SQL table inside the "EXEC() AT" syntax?

Thank you.

Answer 1

You can also use this:

INSERT INTO [LinkedServer].Database.Schema.Table(Attributes)
From
Select (attributes) from Database.Schema.Table as TableAlias

Answer 2

you have to use "OPENQUERY" to do this - see

https://learn.microsoft.com/de-de/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15