1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'C#' started by wsrinivas, Mar 16, 2011.

  1. #1
    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 "
    ----------------------------------------------------------------------------------
     
    wsrinivas, Mar 16, 2011 IP
  2. gotlivechat

    gotlivechat Member

    Messages:
    518
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    35
    #2
    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.
     
    gotlivechat, Mar 16, 2011 IP
  3. wsrinivas

    wsrinivas Member

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    thanks for the response but CSV is not an Option
     
    wsrinivas, Mar 21, 2011 IP
  4. miguelf

    miguelf Member

    Messages:
    98
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    48
    #4
    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.
     
    miguelf, Apr 6, 2011 IP
  5. neil12345

    neil12345 Peon

    Messages:
    24
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
  6. convertin

    convertin Greenhorn

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #6
    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)
     
    convertin, Jun 1, 2011 IP
  7. unknownpray

    unknownpray Active Member

    Messages:
    3,832
    Likes Received:
    14
    Best Answers:
    0
    Trophy Points:
    70
    #7
    you can make this with the help of c#
     
    unknownpray, Jul 1, 2011 IP