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,
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…
scott was too quick
Thanks to both of you…
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 rdS.com/tadpole and written 100% in Xojo
Yea but Tadpole is demo version and sqlitebrowser is free and finale app which you can use.
SQLiteStudio.pl is my favorite at the moment and is also free.
[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]