合并来自不同数据库的sqlite中的两个表 [英] Merging two tables in sqlite from different database

问题描述

我需要根据一个公共列合并 sqlite 中的两个表.问题是两个表都属于不同的数据库.那么，在这里合并表格的有效方法是什么?

示例表将是这样的，并具有所需的结果.但问题是这两个表在不同的数据库中.

表 1:Employee_Pro_Profile列:Emp_Id、Emp_Name、Emp_Sal表 2:Employee_Personal_Profile列:Emp_Id、Emp_Home_Address、Emp_Phone结果表:Employee_Complete列:Emp_Id、Emp_Name、Emp_Sal、Emp_Home_Address、Emp_Phone

解决方案

好吧，首先您必须将数据库附加到您当前的连接.

SQLite 通过使用 ATTACH 为您提供此功能.

ATTACH DATABASE 语句将另一个数据库文件添加到当前数据库连接.附加链接

运行这个:

附加数据库DatabaseA.db作为DbA；将数据库 DatabaseB.db 附加为 DbB；

现在您可以像处理表一样引用数据库...

选择*来自数据库A.表1 A内连接DbB.Table2 B on B.Emp_Id = A.Emp_Id;

可以同时附加到单个数据库连接的数据库数量是有限制的.

如果出现问题，请检查您的设置，标志是:

<块引用>

#define SQLITE_LIMIT_ATTACHED 7//SQLITE_LIMIT_ATTACHED - 附加数据库的最大数量.

I need to merge two tables in sqlite based on a common column. The problem is both the tables belong to different databases. So, what would be an efficient way to merge the tables here?

A sample table would be like this with the desired result. But the problem is these two tables are in different databases.

Table 1: Employee_Pro_Profile
Columns: Emp_Id, Emp_Name, Emp_Sal

Table 2: Employee_Personal_Profile
Columns: Emp_Id, Emp_Home_Address, Emp_Phone

Resulting Table: Employee_Complete
Columns: Emp_Id, Emp_Name, Emp_Sal, Emp_Home_Address, Emp_Phone

解决方案

Okey first you have to attach the databases, to your current connection.

SQLite give you this by using ATTACH.

The ATTACH DATABASE statement adds another database file to the current database connection. ATTACH LINK

Run this:

attach database DatabaseA.db as DbA;
attach database DatabaseB.db as DbB;

Now you can reference the databases as you do with tables...

select
  *
from
  DbA.Table1 A
  inner join 
  DbB.Table2 B on B.Emp_Id = A.Emp_Id;

There is a limit to the number of databases that can be simultaneously attached to a single database connection.

Check your settings if something goes wrong, the flag is:

#define SQLITE_LIMIT_ATTACHED                  7
// SQLITE_LIMIT_ATTACHED - The maximum number of attached databases.

这篇关于合并来自不同数据库的sqlite中的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！