在项目开发中,有时需要在两个数据库之间进行数据的操作,下面给出一例程来实现上述操作。
有这样两个数据库 MyDatabase.db 和 YourDatabase.db
MyDatabase.db中建立表test,如下:
YourDatabase.db中表test1和表test2,分别插入数据如下:
和
插入
(1)下面把上述YourDatabase.db两表中TestName插入到 MyDatabase.db 的test表中。
执行的SQL语句如下:
DELETE FROM TEST;
ATTACH DATABASE 'd:\YourDatabase.db' As 'ChildDatabase';
INSERT INTO test(ID,Test1Name,Test2Name) VALUES(1,(SELECT TestName from ChildDatabase.[test1] where id==1),(SELECT TestName from ChildDatabase.[test2] where id==1));
DETACH DATABASE ChildDatabase;
接下来是程序的实现过程:
#include "stdafx.h"
#include <stdlib.h>
#include <stdio.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for(i=0; i<argc; i++)
{
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[])
{
sqlite3 *db1,*db2;
char *zErrMsg = 0;
int rc1=0,rc2=0;
char *sql;
/* Open database */
rc1 = sqlite3_open("D:\\YourDatabase.db", &db1);
rc2 = sqlite3_open("D:\\MyDatabase.db", &db2);
if( rc1)
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db1));
exit(0);
}
else
{
fprintf(stdout, "Opened database successfully\n");
}
if( rc2)
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db2));
exit(0);
}
else
{
fprintf(stdout, "Opened database successfully\n");
}
sql ="DELETE FROM TEST;ATTACH DATABASE 'D:\\YourDatabase.db' As 'ChildDatabase';INSERT INTO test(ID,Test1Name,Test2Name) VALUES(1,(SELECT TestName from ChildDatabase.[test1] where id==1),(SELECT TestName from ChildDatabase.[test2] where id==1));DETACH DATABASE ChildDatabase;";
/* Execute SQL statement */
rc2 = sqlite3_exec(db2, sql, callback, 0, &zErrMsg);
if( rc2 != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
else
{
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db1);
sqlite3_close(db2);
system("pause");
return 0;
}
执行结果为:
查询
在表test的Test1Name查找表test1里的TestName名称相同的条目
SQL语句:
ATTACH DATABASE 'd:\YourDatabase.db' As 'ChildDatabase';
SELECT *FROM test WHERE Test1Name==(SELECT TestName FROM ChildDatabase.[test1]);
DETACH DATABASE ChildDatabase;
执行结果: