How to transfer data from datable to a database all at once.

I have the following code from a VB.NET project that loads some data from a MySQL table into a datatable then inserts the data into another MySQL table on a different server. When loading the data into the datatable, it loads with a simple one-line command, dtTmp.Load(). Then, to insert into the other table, I have to cycle through each row and insert one record at a time. Does anyone know of a more efficient way to insert the data all at once rather then one record at a time. I experimented with table adapters but gave up after 4 unsuccessful hours.

               Dim cnM As New MySqlConnection(CS_MASTER)
               Dim cnA As New MySqlConnection(CS_ARCHIVE)
               Dim cmd As MySqlCommand
               Dim rst1 As MySqlDataReader
               Dim strSQL As String
               Dim dtTmp As New DataTable("temp")

		'loads the source data into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the source datatable to the new database
		'THIS IS WHERE I'D LIKE TO LOAD WITHOUT CYCLING THROUH EACH ROW.
                cnA.Open()
                For Each dtRow In dtTmp.Rows
                    strSQL = "INSERT INTO tbllkt_trans_archive (Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID)" & _
                             " SELECT " & dtRow(0) & " AS Expr0, '" & dtRow(1) & "' AS Expr1, '" & dtRow(2) & "' AS Expr2, " & dtRow(3) & " AS Expr3, '" & Format(dtRow(4), "yyyy-MM-dd hh:mm:ss") & "' AS Expr4, '" & dtRow(5) & "' AS Expr5, '" & dtRow(6) & "' AS Expr6"
                    cmd = New MySqlCommand(strSQL, cnA)
                    cmd.ExecuteNonQuery()
                Next
                cnA.Close()
                cmd = Nothing

Open in new window

LVL 1
pdi656Asked:
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.

gamarrojgqCommented:
Hi,

DataTableAdapter with CommandBuilder will help you and create all the Insert/Update/Delete statements for you, the only thing you need is to know the Select command that populates the Datatable the first time and that your Table have a Primary Key.

Here is an example of how to do it
Dim conConnection As OleConnection("YOURSTRINGCONNECTION")
        conConnection.Open()
        Dim strCommand as String

        'This is necesary for the CommandBuilder to Create the Insert/Update/Delete statements
        strCommand = "Select * From YourTable"

        Dim daAdapter As New OleDataAdapter(strComand, conConnection)
        Try
            Dim dcbCommand As New OleCommandBuilder(daAdapter)
            daAdapter.Update(dtData)
        Catch ex As Exception
            'Do something with the exception
        Finally
            daAdapter.Dispose()
        End Try

Open in new window

0
gamarrojgqCommented:
And here is how should be using your code example
Dim cnM As New MySqlConnection(CS_MASTER)
               Dim cnA As New MySqlConnection(CS_ARCHIVE)
               Dim cmd As MySqlCommand
               Dim rst1 As MySqlDataReader
               Dim strSQL As String
               Dim dtTmp As New DataTable("temp")

		'loads the source data into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()


                'UPDATE USING DATAADAPTER
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
			
                cnA.Open()
		Dim daAdapter as New MySqlDataAdapter(strSQL, cndA)

                Dim dcbCommand As New SqlCommandBuilder(daAdapter)
                daAdapter.Update(dtData)

                ''Inserts the source datatable to the new database
		''THIS IS WHERE I'D LIKE TO LOAD WITHOUT CYCLING THROUH EACH ROW.
                'For Each dtRow In dtTmp.Rows
                '    strSQL = "INSERT INTO tbllkt_trans_archive (Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID)" & _
                '             " SELECT " & dtRow(0) & " AS Expr0, '" & dtRow(1) & "' AS Expr1, '" & dtRow(2) & "' AS Expr2, " & dtRow(3) & " AS Expr3, '" & Format(dtRow(4), "yyyy-MM-dd hh:mm:ss") & "' AS Expr4, '" & dtRow(5) & "' AS Expr5, '" & dtRow(6) & "' AS Expr6"
                '    cmd = New MySqlCommand(strSQL, cnA)
                '    cmd.ExecuteNonQuery()
                'Next

                cnA.Close()
                'cmd = Nothing

Open in new window

0
pdi656Author Commented:
Thank you for the examples - especially the modified version using my original code. That makes it real easy to understand. The good news is that it didn't error out on me. My original attempts using a data adapter kept kicking out errors. The bad news is that the data wasn't inserted into the MySQL table as I had anticipated. Any more ideas?

Here's my revised code:
                'loads the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_log"
                cnA.Open()
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

                daAdapter.Update(dtTmp)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

Open in new window

0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

gamarrojgqCommented:
Ok, according to your code, there is nothing to INSERT/UPDATE/DELETE, since the rows  do not change, and you are trying to update the SAME TABLE

I saw you original post, and based on that, the problem will be the destination TABLE, you should use tbllkt_trans_archive in the second Select Command

       'Inserts the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive"
0
pdi656Author Commented:
They are different tables on different database servers. The big picture is that I have multiple PCs (master dbs) that I want to ship their transactional data nightly to a common server (archive database).

I tried your latest suggestion (which makes complete sense to me as to why it should be changed), but that didn't seem to insert the records either. Thank you for sticking with this. I appreciate your help. I think this path is close, but not quite there yet.
0
gamarrojgqCommented:
Ok, I understand now, the problem is that the UPDATE method will look for CHANGED rows, but since no rows has been changed it will not do any insert even if the target table does not have the rows because is in another server.

So, you can do something like this (but it will force you to CYCLING THROUH EACH ROW) at least to change  the rows  status


'loads the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Inserts the master temp datatable to the archive database
		'SELECT NO RECORDS, JUST OBTAIN THE FIELDS			
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive WHERE 1=0"
                cnA.Open()

		'Use another Datable 
		Dim dtTarget As Datatable		
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)

		'Fill the datatable from ARCHIVE DB, this will gives an empty datatable
		daAdapter.Fill(dtTarget)

		'Add/Insert the Rows from MASTER DB to ARCHIVE DB
		For each drTmpRow in dtTmp.Rows
		     dtTarget.ImportRow(drTmpRow)
		Next

                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

		'Updte the ARCHIVE DB
                daAdapter.Update(dtTarget)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

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
pdi656Author Commented:
You're right...the row state is set to unchanged. I like where you headed me with that code - it got me thinking. I did a little research and found a way to alter the row state in my current datable without have to insert them into a second table. It cycled through 4k rows in a blink of an eye. The INSERT code then worked perfectly. The row state was the key. Here's my completed code, in case anyone else is curious in the future:

                'SELECT the master data to be archived into a temp datatable
                dtTmp.Clear() 'clears the temp datatable of data
                strSQL = " SELECT ID AS Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, '" & stMASTNAME & "' AS Comp_ID" & _
                         " FROM tbllkt_trans_log" & _
                         " WHERE Test_Date < Date_Sub(CURDATE(), INTERVAL " & bInt & " DAY);"
                cnM.Open()
                cmd = New MySqlCommand(strSQL, cnM)
                rst1 = cmd.ExecuteReader()
                dtTmp.Load(rst1) 'loads the temp datatable
                rst1 = Nothing
                cmd = Nothing
                cnM.Close()

                'Loop the rows of the datatable and set the row status to ADDED
                'NOTE This is required for the subsequent INSERT to work.
                For Each dr As DataRow In dtTmp.Rows
                    dr.SetAdded()
                Next

                'INSERT the master temp datatable to the archive database
                strSQL = " SELECT Source_ID, MFGNUM_0, Part_Number, Test_Results, Format(Test_Date, 'yyyy-MM-dd hh:mm:ss') AS Test_Date, Test_Cell, Comp_ID" & _
                         " FROM tbllkt_trans_archive"
                cnA.Open()
                Dim daAdapter As New MySqlDataAdapter(strSQL, cnA)
                Dim dcbCommand As New MySqlCommandBuilder(daAdapter)

                daAdapter.Update(dtTmp)
                daAdapter.Dispose()
                dcbCommand = Nothing
                cnA.Close()

Open in new window


Thanks again for all of your help!
0
gamarrojgqCommented:
Ok, great!!! glad to help
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.