Duplicate a Record in MySql Table with Auto Increment Key

Introduction

This tip discusses how to copy a specific record in a MYSql table with auto increment key.

Background

I have worked with MySql for several years now and there have been cases where I needed to copy (or duplicate) in a table where an auto increment key exists.

The following function for VB.NET is a solution that has been helping me a lot recently.

So, I decided to share it with you all!

Using the Code

This is public shared Boolean function and you can call it from your VB.NET code:  

Public Shared Function copy_recod(ByVal tbl As String, _
		ByVal tbl_id_name As String, ByVal tbl_id_value As Long) As Boolean
        Dim dup_query As String = Nothing
        Dim tbl_dat As New DataTable
        Dim next_id As Long = 0
        Dim copy_query As String = Nothing
        Dim col_value As String = Nothing

        Dim did_it As Boolean = False

        dup_query = "select * from " & tbl & " _
        where " & tbl_id_name & "=" & tbl_id_value
        tbl_dat = retriveDataToDataGrid(dup_query)

        If Not tbl_dat Is Nothing Then
            ActionRecord("LOCK TABLES " & tbl & " WRITE")
            ActionRecord("insert into " & tbl & _
            "(" & tbl_id_name & ")values( NULL)")

            next_id = Val(get_single_field("select MAX_
            (" & tbl_id_name & ") from " & tbl, 0))

            ActionRecord("UNLOCK TABLES")

            copy_query = "update " & tbl & " set "
            For Each r As DataRow In tbl_dat.Rows
                For Each col As DataColumn In tbl_dat.Columns

                    If col.ColumnName <> tbl_id_name Then
                        col_value = r(col).ToString

                        If InStr(col.DataType.ToString, "String") > _
                        0 Or InStr(col.DataType.ToString, "Date") > 0 Then
                            copy_query = copy_query & col.ColumnName & _
                            "='" & col_value & "',"
                        Else

                            If IsDBNull(col_value) Or col_value.Length <= 0 Then col_value = 0


                            copy_query = copy_query & col.ColumnName & _
                            "=" & col_value & ","
                        End If

                    End If

                Next
            Next

            copy_query = copy_query.Remove(copy_query.LastIndexOf(","), 1)

            If Right(copy_query, 1) = "=" Then
                copy_query = copy_query & "'" & 0 & "'"
            End If

            copy_query = copy_query & " where " & _
            tbl_id_name & "=" & next_id
            If ActionRecord(copy_query) > 0 Then
                did_it = True
            End If
        End If

        Return did_it

Points of Interest

In order for me to create this open function that could work for any table, regardless of number of fields, I had to use enough variables and have checkpoints to assure a clean and workable code.

The important part of this code is where it recognizes the field type and creates proper SQL command for copy.

ActionRecord() function is my function to run a query. So you can change that with your command or function to launch the update query.

History

• 27^th July, 2012: Initial version