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.

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
– lysdexia, Commented Feb 14, 2017 at 14:19

Community · Accepted Answer · 2017-05-23 12:24:26Z

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.

C. Griffin · Accepted Answer · 2017-02-14 16:58:00Z

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, 2017 at 16:58

C. Griffin

6811 gold badge13 silver badges32 bronze badges

1 Comment

lysdexia Over a year ago

That's where I went first, but you will always be my Fujiko.

Collectives™ on Stack Overflow

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

2 Answers 2

Slow and Probably Awful Answer

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

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Slow and Probably Awful Answer

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

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related