SQl - Copy tables from a linked server to a table on SQL 2008 R2

Question

I need to copy all tables from a linked server to a table on SQL 2008 R2. I have tried the following but it gives an error. The database that I need the tables copied to is called Apples. My linked server is called 'MYSQL' and database on the linked server is called 'drupaltest':

select * into Apples
from OPENQUERY(MYSQL, 'SELECT * FROM MYSQL.drupaltest')

This is the error: OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.28-log]Table 'mysql.drupaltest' doesn't exist". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQL".

Any ideas?

Answer 1

You say that the database is named drupaltest, and the error is referring to a table.

Assuming that MYSQL is just your linked server name, try to specify the table...

... from OPENQUERY(MYSQL, 'SELECT * FROM drupaltest.table')