Re: [sqlite] Copying a table between databases

[Dennis Volodomanov]

Thank you Derrell and Cory,
I can create triggers when I create the database file and I'm not using 
indexes in this particular program, so that seems like the way to go. 
Wouldn't a function like sqlite3_copytable(sqlite3 *pDest, sqlite3 *pSrc, 
...) be a good idea? Or it would make SQLite more complex and thus go 
against the main principle?

Just wondering... :-)
  Dennis
// MCP, MCSD
// ASP Developer Member
// Software for animal shelters!
// www.smartpethealth.com
// www.amazingfiles.com
----- Original Message ----- 
From: "Cory Nelson" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>; <[EMAIL PROTECTED]>
Sent: Friday, April 15, 2005 12:33 AM
Subject: Re: [sqlite] Copying a table between databases

field types are retained but indexes and triggers won't be copied
On 4/14/05, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
"Dennis Volodomanov" <[EMAIL PROTECTED]> writes:
> So, "CREATE TABLE AS" will basically duplicate the table that I have (if
> I say for example, "CREATE TABLE AS myNewTable AS SELECT * FROM
> myOldTable")? Sounds good if that's true :-)
You have one too many "AS" in your example.  (No "AS" between CREATE TABLE 
and
the table name.  Try it this way:

  CREATE TABLE myNewTable AS SELECT * FROM myOldTable;
In sqlite 2.8.* you lose the field types (which were just comments in the 
2.8
series anyway).  I suspect that in 3.0.* the field types are retained, but 
I
haven't tested it.  You can verify with something like this:

    % sqlite :memory:
    SQLite version 2.8.16
    Enter ".help" for instructions
    sqlite> create table x (i integer);
    sqlite> create table y as select * from x;
    sqlite> .dump
    BEGIN TRANSACTION;
    create table x (i integer);
    CREATE TABLE y(i);
    COMMIT;
    sqlite>
Derrell

--
Cory Nelson
http://www.int64.org

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 14-Apr-05

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 14-Apr-05