On 2015-10-06 04:52 PM, H?ctor Fiandor wrote:
> Dear fellows:
>
>
>
> I handle my applictions with Lazarus and use BD as sqlite. Sometimes I have
> to obtain a copy of an sqlite table and now I use the method of read and
> write, but I want to know if exists and SQL command that do the same with
> less efforts.
Hi H?ctor,
Copying a table via SQL is rather easy with respect to the data... but
tables may have Indices, Constraints and Triggers that were added after
creation or depend on other tables. Tables might also have an
Auto-Increment value set on a primary key and kept in another table
(sqlite_sequence), etc.
If your table has no Auto-increment explicitly defined, and no external
or table-dependent constraints, then the following SQL will happily
produce a copy of the basic table layout and its contents:
CREATE TABLE newTableName AS SELECT * FROM oldTableName;
If you fancy keeping the original Schema, then an extra step is needed,
which entail reading the old SQL, replacing the table name and then
applying it and copying data, which in some pseudo-code might look like
this:
Open DB;
$SQL = ReadFromSQLite(" SELECT sql FROM sqlite_master WHERE type='table'
AND name='oldTableName'; ");
replace($SQL, "CREATE TABLE oldTableName", "CREATE TABLE newTableName");
// Beware the old name might be enclosed in quotes or such.
if sqlite-execute($SQL) then
sqlite_execute(" INSERT INTO newTableName SELECT * FROM
oldTableName; ");
Note that it is better to determine the fields returned by a "SELECT *"
statement and using that string rather than the "*" because technically,
there is no guarantee that the order of fields returned by * will be the
same as it was defined in the table. (It mostly /is/ courtesy of
convention, but it is not guaranteed behaviour).
You can expand the above code to check and add external constraints,
indices and triggers. Use pragma foreign_keys = OFF; at the start where
you are duplicating tables with foreign key constraints.
If you are copying a table to a different DB, then the name need not be
replaced (obviously).
The SQLite CLI can dump an entire database to SQL if you fancy copying
the DB in total.
Hope some of this helped,
Cheers!
Ryan
