SQLite Duplicate Record

  1. last week

    Bill

    Sep 12 Pre-Release Testers, Xojo Pro Longview Texas

    I need to build a temporary table from a complex selection of records from a SQLite table.

    Is there a way to duplicate the record from one table into another table without manually copying each individual field?

    thanks,

    look at INSERT syntax with a SELECT … INSERT INTO table SELECT ..
    look at insert form #2...

  2. scott b

    Sep 12 Pre-Release Testers, Xojo Pro Answer local coffee shop

    look at INSERT syntax with a SELECT … INSERT INTO table SELECT ..
    look at insert form #2...

  3. Dave S

    Sep 12 San Diego, California USA

    scott was too quick :)

  4. Bill

    Sep 12 Pre-Release Testers, Xojo Pro Longview Texas

    Thanks to both of you....

  5. extra option would be to use sqlitebrowser.org app for mac/win and play with db and later to transfer on simulator/device to use in your app :)

  6. Dave S

    Sep 12 San Diego, California USA

    @Bogdan P extra option would be to use sqlitebrowser.org app for mac/win and play with db and later to transfer on simulator/device to use in your app :)

    or even better.... Tadpole for mac/win available at rdsisemore.com/tadpole and written 100% in Xojo :D

  7. Yea but Tadpole is demo version and sqlitebrowser is free and finale app which you can use.

  8. 7 days ago

    SQLiteStudio.pl is my favorite at the moment and is also free.

  9. 5 days ago

    Hal G

    Sep 14 Pre-Release Testers, Xojo Pro Orlando, FL

    @BillPlunkett I need to build a temporary table from a complex selection of records from a SQLite table.

    Is there a way to duplicate the record from one table into another table without manually copying each individual field?

    thanks,

    Here's some code for you that results with an array of NewTableKeys. You'll need to change xol.TextUUIDGenerate to your own UUID generating code. I think I use Kem's UUID Generator...

      Dim OldTableKey, NewTableKey, NewTableKeys() As String
      
      // Find the Record
      SQL = "SELECT * FROM " + TableName + " WHERE " + TableKeyName + " = ?"
      Dim PSDuplicate As PreparedSQLStatement
      PSDuplicate = Database.Prepare( SQL )
      
      PSDuplicate.Bind( 0, TableKey )
      PSDuplicate.BindType( 0, MySQLPreparedStatement.MYSQL_TYPE_STRING )
      
      Records = PSDuplicate.SQLSelect
      
      // Duplicates Each Record from a Previous Query. Assumes the TableKey field is in the Recordset.
      If Records <> Nil Then
        For i As Integer = 1 To Records.RecordCount
          
          OldTableKey = Records.Field( TableKeyName ).StringValue
          If OldTableKey <> "" Then
            
            // Dup the record and set the TableKey to be a Unique value.
            NewTableKey = xol.TextUUIDGenerate
            NewTableKeys.Append( NewTableKey )
            SQL = "CREATE TEMPORARY TABLE tmptableDup SELECT * FROM " + TableName + " WHERE " + TableKeyName + " = " + OldTableKey.StringQuoteSingle + " ; "
            Database.SQLExecute( SQL )
            SQL = "UPDATE tmptableDup SET " + TableKeyName + " = " + NewTableKey.StringQuoteSingle + " ; "
            Database.SQLExecute( SQL )
            SQL = "INSERT INTO " + TableName + " SELECT * FROM tmptableDup ; " 
            Database.SQLExecute( SQL )
            SQL = "DROP TEMPORARY TABLE If EXISTS tmptableDup ; "
            Database.SQLExecute( SQL )
          End If
          
        Next
      End If

or Sign Up to reply!