Luuk wrote:
> On 17-05-2012 11:04, YAN HONG YE wrote:
>> I have two db files:
>>
>> sqlite3 *db1;
>> sqlite3 *db2;
>> rc1 = sqlite3_open("myfile1", &db1);
>> rc2 = sqlite3_open("myfile2", &db2);
>>
>> I want to copy db1.table1 to db2 file, but I don't know how to do?
>
> sqlite myfile1
> sqlite> attach database 'myfile2' as db2;
> sqlite> create table db2.table1 as select * from main.table1;
... but note that new table will have stripped schema (column names will be
same, but no exact types, and all constraints, indexes, triggers,... will be
stripped):
sqlite> create table t(a int64 not null, b text check(b<>'foobar') collate
nocase /* comment */, c datetime unique, d varchar(64));
sqlite> create table u as select * from t;
sqlite> .schema
CREATE TABLE t(a int64 not null, b text check(b<>'foobar') collate nocase /*
comment */, c datetime unique, d varchar(64));
CREATE TABLE u(a INT,b TEXT,c NUM,d TEXT);
If you want to copy whole database (all tables), you'd better use backup API.
If you want to copy just one table, but need to keep schema:
You can try read original table definition from (SELECT sql FROM sqlite_master
WHERE tbl_name COLLATE NOCASE = ? AND type = 'table'), feed it to new db
connection, and then use
INSERT INTO db2.table1 SELECT * FROM main.table1;
but that may result in problems with some constraints [esp. foreign keys] (and
messing with internal machinery [sqlite_master] may be not very safe with
respect of future sqlite compatibility).
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
