distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by azazelahh
5 months ago
Category: Import/Export
2 posts since Mon, 07 Apr 14
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?
Written by ansgar
4 months ago
4967 posts since Fri, 07 Apr 06
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.

Written by kalvaro
4 months ago
591 posts since Thu, 29 Nov 07
@azazelahh Please don't forget that SQL is a server-side language.
Written by azazelahh
4 months ago
2 posts since Mon, 07 Apr 14
thank you for the replies
 

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