Export a record from a MySQL db to a SQL Server db

[expired user #7974]'s profile image [expired user #7974] posted 10 years ago in Import/Export Permalink
Hi,

I have established two connections: one to a MySQL database and the other one to a SQL Server database. I used two different sets of credentials for the two databases since a single common set of login credentials for those two databases does not exist.

I can browse and edit the databases independent of each other with no issue. However, I'd like to copy a few records from the MySQL database to the SQL Server database.

I tried something like this:

INSERT INTO dbo.Locations (Name, Description)
SELECT name, NULL
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='mysql_db' AND TABLE_SCHEMA='dbo' AND TABLE_NAME='locations';
WHERE id = 2

I got an error: SQL Error (207): Invalid column name 'name'.

Is my syntax wrong? Can someone show an example of the syntax that would work, please?
ansgar's profile image ansgar posted 10 years ago Permalink
You cannot select something from one server and then insert that into another one in one query. The "Export SQL" dialog should allow that at some point in the future, but I'm afraid that fails in most attempts due to incompatible SQL dialects. Though in your case you could try it out, when you have just rows to copy.

Another way is to copy rows from one server per export grid rows dialog should and use SQL inserts output. Then, focus the other server and paste/run these inserts there.

kalvaro's profile image kalvaro posted 10 years ago Permalink
@azazelahh Please don't forget that SQL is a server-side language.
[expired user #7974]'s profile image [expired user #7974] posted 10 years ago Permalink
thank you for the replies

Please login to leave a reply, or register at first.