SSMS "SELECT * INTO" from MSSQL Table to a Linked MySQL Server

Question

I need to transfer some tables from a MSSQL server to a MySQL server.

I have successfully connected SSMS on a windows host to my MySQL server on my linux box. The "linked server" appears in the object explorer and I am able to perform queries on the MySQL server, vis.

sql select * from openquery(LINKED_SERVER_NAME, "select * from mysqltable.customers");

I'm an utter noob with MSSQL and am somewhat confused by openquery. Is the query "read only", or can I actually select the contents of an MSSQL table into a table on the linked server?

All the MSDN pages I have read are reminiscent of those old break dancing instruction videos from the 80's. I.E. a very terse example, followed by utter madness.

I have seen excellent examples showing how to use openquery to select the contents of the remote MySQL database into an MSSQL database. Unfortunately I can't figure out if there is a way to reverse that syntax.

Really sorry if this is a stupid question, but I'm just not getting any traction.

http://mohtasebi.com/sql/2011/12/10/linking-mysql-and-sql-server-using-linked-server.html — McNets, Feb 14 '17 at 00:36
It is becoming apparent that in your insert example, one has to do an "empty" query that returns the fieldnames you wish to insert. This is a convoluted mess (SSMS, not your example). I'll definitely show my work for the next guy. This is awful. — lysdexia, Feb 14 '17 at 14:19

score 0 · Answer 1 · edited May 23 '17 at 12:24

Slow and Probably Awful Answer

Using the clues provided by @McNets / Amir Mohtasebi and @bluefeet I have been able to kludge something together.

I punted on trying to directly copy the MSSQL table into the MySQL database. I'm still not clear on whether it is even a possibility with openquery.

Loop Over Query Results and Insert Records One-By-One

declare @i int=0
declare @count int
declare @idcustomer int
declare @customerType int 

select @count = count(*) from customers

while (@i < @count)
begin
    select @idcustomer = idCustomer, @customerType = customerType from customers
    order by idcustomer asc offset @i rows fetch next 1 rows only

    insert openquery(
        REMOTE_STORE,
        'select idcustomer, customerType from customers where 1=0'
    ) values(
        @idcustomer,
        @customerType)

    set @i = @i + 1;
end

As one would expect, this takes just south of forever, and requires set-up for each and every field. If anyone has a better technique (and that is probably anyone who uses T-SQL), I'd sure love to see it.

score 0 · Answer 2 · answered Feb 14 '17 at 16:58

0

Did you just try the basic queries in the syntax of:

select * into newtable from openquery(LINKED_SERVER_NAME, "select * from mysqltable.customers");

OR

select * into newtable from [linkedserver].[database].[schema].[tablename]

answered Feb 14 '17 at 16:58

C. Griffin

681
1
12
32

That's where I went first, but you will always be my Fujiko. – lysdexia Feb 14 '17 at 17:10

SSMS "SELECT * INTO" from MSSQL Table to a Linked MySQL Server

2 Answers2

Slow and Probably Awful Answer

Loop Over Query Results and Insert Records One-By-One