Click here to Skip to main content
15,031,937 members
Articles / Database Development / MySQL
Article
Posted 27 Jul 2012

Tagged as

Stats

12.2K views
1 bookmarked

Duplicate a Record in MySql Table with Auto Increment Key

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
27 Jul 2012CPOL
How to 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:  

VB.NET
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

  • 27th July, 2012: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kourosh K Tari
Chief Technology Officer
United States United States
I sold my beloved racing bicycle to buy my first computer, a Sinclair home computer! That was over 30 years ago and I am still in love with what became my profession, and quite honestly my calling! I have received my BS and MS in Mathematics and Computer science and have been working in so many fields of software development, system architecture, and design and I have most enjoyed teaching and writing about programming languages and fiddling with new technologies!
I believe life is too short to ignore learning a programming language!

Comments and Discussions

 
-- There are no messages in this forum --