Migrate Data from MySQL database to MS SQL based on a query

Hi Gurus

I am looking to Migrate Data from MySQL database to MS SQL based on a query

tblAnalysis is located on another server using MS SQL
Please suggest if you have any faster solutions.

Thanks
Sri

My ASP script is as follows:


----------------------------------------------------------------------------------
Set MTFDBConn = Server.CreateObject("ADODB.Connection")
strConnection = "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=xxx;pwd=xxx;port=3306;database=xxx;"
MTFDBConn.Open strConnection
strSelect = " SELECT `tblitemsonorder`.`txtPartNO`, `tblitemsonorder`.`txtProductName`, sum(`tblitemsonorder`.`txtQTY`) as noofsold "
strSelect = strSelect & " FROM `tblitemsonorder` WHERE `tblitemsonorder`.`txtOrderID` in "
strSelect = strSelect & " (SELECT `tblorders`.`OrderID` FROM `tblorders` "
strSelect = strSelect & " WHERE `tblorders`.`txtDateTime` BETWEEN '" & FromDate & "' AND '" & ToDate & "' "
strSelect = strSelect & " AND `tblorders`.`txtIsConfirmed` = '1' ) AND "
strSelect = strSelect & " (`tblitemsonorder`.`txtPartNO` like '" & txtSearchTerm & "') "
strSelect = strSelect & " GROUP BY `tblitemsonorder`.`txtPartNO`, `tblitemsonorder`.`txtProductName` "
strSelect = strSelect & " order by noofsold desc;"
set rsProducts = server.CreateObject("Adodb.Recordset")
'response.write strSelect
rsProducts.open strSelect, MTFDBConn
if rsProducts.eof = false then
do while rsProducts.eof = false
dbconn.execute("INSERT INTO tblAnalysis (Partno ,Productname,Qty) VALUES ('" & replaceApos(rsProducts("txtPartNO")) & "','" & replaceApos(rsProducts("txtProductName")) & "' ,'" & replaceApos(rsProducts("noofsold")) & "'); ")
rsProducts.movenext
loop
End if
rsProducts.close
set rsProducts = nothing
MTFDBConn.close
set MTFDBConn = nothing

response.write "Data Transfer Done "
----------------------------------------------------------------------------------

 

Depending on the size of the database and/or size of the asp page, it could end up timing out. Better solution would be to export the data in a csv file and then import on other db locally.

 

thanks for the response but CSV is not an Option

 

This should be pretty straight forward with an external tool. You can read the rows, maybe 100 at a time and then insert then into the new DB. Using C# will make this easy.

 

Better to use DTS import export wizard check here

http://technet.microsoft.com/en-us/library/cc966396.aspx

or use this tool

http://weblogs.asp.net/guybarrette/archive/2010/01/12/mysql-to-sql-server-migration-tools.aspx

 

Try this tool:

http://www.convert-in.com/sql2mss.htm

It can filter data to migrate via sophisticated SQL-queries and has good performance (about 200 records per second on average P-IV system)

 

you can make this with the help of c#