8

I need to build a procedure to read data from SQL Server and insert it into MySql. More I also need another procedure to read data from MySql Server and insert it into SQL Server.

I never done such a thing. I can't think of a way to get it done. Can someone tell me how can I do so such a thing?

I appreciate at least an idea where I can search it.

I have been googling this but did not find much of help.

I have Scribe software I am not sure if there is a future in Scribe that can help me with this?

Note this procedure Will be automatically run so transfer data or receive data from the source.

Mike
  • 2,735
  • 11
  • 44
  • 68

2 Answers2

7

The best way to achieve this is to create a Linked Server in SQL Server, this way you can query both SQL Server and MySQL from the same place. You can write a stored procedure to access both or a Job to run on specific schedule.

Update: Sample INSERT Query

INSERT OPENQUERY (SERVNAME, 'SELECT subject, notes FROM cms.mysql_table') 
SELECT subject, notes FROM sql_server_table
Mike
  • 2,735
  • 11
  • 44
  • 68
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • Thanks for your feedback. I have Linked Server already established and I do use OPENQUERY to pull data but How do I use it to insert the data that I pulled into a different server? I think using open query I can pull data from mysql and then insert it into SQL but how to I do it the other way around? – Mike Jun 07 '13 at 01:48
  • It should be very similar, if your select query is something like `select * from openquery(MYSQLSRV, 'select * from table')`, your insert query will be something like `INSERT OPENQUERY (MYSQLSRV, 'SELECT field FROM table') VALUES ('my value');` – Yuriy Galanter Jun 07 '13 at 01:55
  • I am trying to get the insert to work by reading data from SQL Server into MySql but I can't get this to work. this is what I have done, can you please look into my syntax SELECT subject, notes FROM sql_server_table AS s INSERT OPENQUERY (SERVNAME, 'INSERT INTO cms.mysql_table(subject,notes)') VALUES (s.subject, s.notes) – Mike Jun 07 '13 at 02:20
  • I've updated my answer with the sample INSERT query, could u give it a try? – Yuriy Galanter Jun 07 '13 at 02:31
  • It did not work. I get and error with this message at the end The user did not have permission to write to the column. – Mike Jun 07 '13 at 02:44
  • Make sure your linked server permission allows writing to the DB/Table/Column for that user. It looks like currently it's a read-only permission – Yuriy Galanter Jun 07 '13 at 02:46
  • Where do I change the permission from? the linked server? the mysql user has full permission unless the linked server does not have a write permission I don't know whereto change that from – Mike Jun 07 '13 at 02:50
  • You need to map remote user to local one: `EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVNAME', @useself=N'False', @locallogin= , @rmtuser= , @rmtpassword= ` – Yuriy Galanter Jun 07 '13 at 03:01
  • FYI: The like server is already established. I can read data but I am unable to write. Any way I followed your instruction and I have run that query but I am still having the same issue I can't write data – Mike Jun 07 '13 at 03:11
  • So something is still linked incorrectly. Verify that your MySQL user can write to that table and if so - that it is properly mapped to the currently logged user of SQL Server. The error means that the query is correct and attempt to write is made and denied – Yuriy Galanter Jun 07 '13 at 03:20
  • the mysql user can write for sure because I am using the same user to my php script and it is the same user i use to login my phpmyadmin and i can read/write/delete with it. Does the local user need a write permissions? – Mike Jun 07 '13 at 03:31
  • Okay I found the issue, it is not a permission issue it is conversion issue the notes column in the MySQL table is of a type text when I change it to Char 255 it works but that not a good solution as I cant change my MySQL column type. this is my working qurty after changeing the text column to char --INSERT DATA INTO MySql Table INSERT OPENQUERY (SERVNAME, 'SELECT subject, notes FROM call_managment_system.test') SELECT CAST(subject AS CHAR) AS subject, CAST(notes AS CHAR) AS notes FROM #temp_lasweb – Mike Jun 07 '13 at 04:34
0

You can do like below to achieve your result

SELECT * 
INTO dbo.Users_Import 
FROM OPENQUERY(remotelinkedservername, ‘SELECT * FROM dbo.Users’ )

(source)

Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
azi
  • 1