SQLite Duplicate Record

Databases
1

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,

2

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

3

scott was too quick :slight_smile:

4

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

6

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

7

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

8

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

9

[quote=349975:@Bill Plunkett]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,[/quote]

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…

[code] 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[/code]