Retrieve Rows from a MySql DB and then Insert into Sql Sever DB using Vb.Net


I have a VB.Net project coded with Sql Server for the back -end.
However I just discovered that there is data in a MySql database which I need to retrieve and then insert that data into  a table that is sitting in a SQl server table... The data coming from the MySql DB will be multiple rows.. so is it best to grab all the rows at once and then insert in Sql Server or should i insert each row as it goes through the loop. In other words open MySql Db cionnection, grab the row and then insert it into Sql Server. Of course all of this needs to be done with VB.Net code

I have a connection working both into the Sql Server and MySql DB.

Any help or insight would  be much appreciated
nomar2Asked:
Who is Participating?
 
ITHelper80Connect With a Mentor Commented:
Youll need to use the SQLBulkCopy command.

Try this.
     Dim connect As New MySqlClient.MySqlConnection("Server=localhost;Port=xxx;Database=xxxx;Uid=xxx")
        connect.Open()
 
        Dim sql As MySqlCommand = New MySqlCommand("SELECT sName FROM tVendor where IId = 18", connect)
        Dim ds As DataSet = New DataSet()
        Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
        DataAdapter1.SelectCommand = sql
        DataAdapter1.Fill(ds, "table1")
        connect.Close() 'MYSql Connection
 
        '## Sets query string ##
        Dim cn As New System.Data.SqlClient.SqlConnection("DataSource=SQLServer; DefaultCollection=xxxxx; UserID=xxxx; Password=xxxx;")
        Dim bc As New System.Data.SqlClient.SqlBulkCopy(cn)
        bc.DestinationTableName = "YourTable"
        Try
            cn.Open()
            bc.WriteToServer(ds.Tables(0))
        Catch ex As SqlClient.SqlException
 
        Finally
            cn.Close()
        End Try
 
 
    End Sub

Open in new window

0
 
ITHelper80Commented:
I would retrive the data from the MySQL server and put it into a dataset and then Insert that into the SQL DB all at once

0
 
nomar2Author Commented:
Okay I am testing something with MySql and the dataset in MySql...

        Dim connect As New MySqlClient.MySqlConnection("Server=localhost;Port=xxx;Database=xxxx;Uid=xxx")
connect.Open()

        Dim sql As MySqlCommand = New MySqlCommand("SELECT sName FROM tVendor where IId = 18", connect)
        Dim ds As DataSet = New DataSet()
        Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
        DataAdapter1.SelectCommand = sql
        DataAdapter1.Fill(ds, "table1")
        connect.Close() 'MYSql Connection

This is my code so far..what would I need to do to  get the dataset in Sql Server..any insight would be appreciated

Do I need to open a connection for Sql Server..But how do I handle putting the dataset from MySql ino Sql Server??
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nomar2Author Commented:
ITHELPER80..

That is working..

One quick question ...I have to pull the data from the MYSql Db based on a hardcoded date in my vb.net code...

Dim sql1 As MySqlCommand = New MySqlCommand("SELECT lVenID,dtdate,dPreTaxAmt FROM tVentr where dtDate > '#2009-03-31#'", connect)

the format of the date data as an example in MySql DB is    2004-10-11 00:00:00

When I run the above SQL Command .. I get back all the rows..it seems to ignore the filter..

I will award extra points for this answer to the quick question
0
 
ITHelper80Commented:
Try this


"SELECT lVenID,dtdate,dPreTaxAmt FROM tVentr WHERE DATE(dtDate) > '2009-03-31

Open in new window

0
 
ITHelper80Commented:
Sorry snippet messed up
"SELECT lVenID,dtdate,dPreTaxAmt FROM tVentr WHERE DATE(dtDate) > 2009-03-31'"

Open in new window

0
 
ITHelper80Commented:
3rd time is the charm! Darn MS word....lol
"SELECT lVenID,dtdate,dPreTaxAmt FROM tVentr WHERE DATE(dtDate) > '2009-03-31'"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.