比较两个数据库的新的方法

比较两个结构相同的数据库的不同，开始是把数据库的列的值分别读出来，再进行比较，感觉比较的耗时间。

现在把一个数据库附加到另一个数据库，用SQL语句来读出不同的元素，不只可行否，参考如下博客：

http://blog.sina.com.cn/s/blog_7768d22101012t30.html

 

attach database

已经往内存数据库A插入了10W条数据,我现在想把A导入一个物理文件B(具有相同表结构),是不是应该用attach指令?有没有高人详细指点一下?

sqlite3_open(":memory:", &db);
...
ret = sqlite3_exec(db, "CREATE TABLE new_table1 (id integer primary key autoincrement,name vchar(32))", 0, 0, &zErrMsg);

ret = sqlite3_exec(db,"begin transaction",0,0,&zErrMsg);

ret = sqlite3_exec(db, "insert into new_table1 (name) values ('123');", 0, 0,&zErrMsg);

ret = sqlite3_exec(db,"commit transaction",0,0,&zErrMsg);

到上面,数据库已经插入内存数据库了,然后再怎么导入物理文件B?

sqlite3_open("c:\A.dat", &db);

ret = sqlite3_exec(db,"attach A.dat as new_db2",0,0,&zErrMsg);

ret = sqlite3_exec (db,"insert into new_db2.new_table2 (name) values('name')",0,0,&zErrMsg);

ret = sqlite3_exec(db,"detach new_db2",0,0,&zErrMsg);

if (ret!=SQLITE_OK)
MessageBox("附加数据库失败！");
这样会提示"附加数据库失败!",不知道是那里写错了...

----------------------------------------------------------------------------------------------------------------------------

sqlite_user ：

搞反了，应该把文件数据库attach到内存数据库中。sqlite3_open("c:\A.dat", &db);
这一句是多余的，应该去掉。
ret = sqlite3_exec(db,"attach 'c:\A.dat' as new_db2",0,0,&zErrMsg);
这样就可以从内存数据库中复制数据到文件数据库中了，文件数据库中没有表时使用（注意新表没有索引）：
create table new_db2.new_table1 as select * from new_table1;
有表时使用：
insert into new_db2.new_table1 select * from new_table1;

--------------------------------------------------------------------------------------------------------------------

已经搞定~
ret = sqlite3_exec(db,"attach 'c:\A.dat' as new_db",0,0,&zErrMsg);

ret = sqlite3_exec(db,"begin transaction",0,0,&zErrMsg);

ret = sqlite3_exec(db,"insert into new_db.new_table2 (name) select name from new_table1",0,0,&zErrMsg);

ret = sqlite3_exec(db,"commit transaction",0,0,&zErrMsg);

300万条内存数据,10个字段,大概写入了631MB物理文件

 

 

 

经典SQL语句

select bid from a where bid not in (select bid from b) union select bid from b where bid not in (select bid from a)或者这样也行SELECT a.bid, b.bidFROM a, bWHERE (((a.bid) Not In (select bid from b)) AND ((b.bid) Not In (select bid from a)))这里bid就是商品名字段

 

 

参考：

http://topic.youkuaiyun.com/u/20100821/11/549dc4f0-67a8-44d6-bd09-2b8f7a971e1c.html