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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ITHelper80Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.