如何实现MSSQL中多个数据库共享一张表

介绍

在MSSQL中，如果多个数据库需要共享一张表，如何实现呢？一种方法是使用分布式视图，可是这种方法比较复杂，而且可能会使系统的性能降低。本文将讨论一种更简单的方法，即在同一个MSSQL实例中共享表格。

环境

本文将演示如何在MSSQL Server Management Studio(MSSMS)中使用两个数据库共享一张表。环境如下：

MSSQL Server 2019

MSSMS 18.9.1

步骤

1. 创建一个存储过程

这个存储过程将用于从一个数据库复制表到另一个数据库。在这里，我们将从“db1”数据库中复制表“shared_table”到“db2”数据库中。首先，我们需要在“db1”数据库中创建以下存储过程。

USE db1
GO
CREATE PROCEDURE sp_CopyTableToDB2
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID('db2..shared_table', 'U') IS NOT NULL
    DROP TABLE db2..shared_table;
    SELECT *
    INTO db2..shared_table
    FROM db1..shared_table;
END
GO

上述代码中，我们在“db1”数据库中创建了一个存储过程“sp_CopyTableToDB2”。如果数据库“db2”中已经有了表格“shared_table”，那么我们将先删除这个表格。然后，我们从“db1”中选择所有的数据，插入到“db2”的“shared_table”表格中。

2. 创建作业

我们需要创建一个作业，以便定期运行“sp_CopyTableToDB2”存储过程。以下是创建作业的步骤。

在MSSMS中，展开“SQL Server Agent”文件夹，并右键单击“作业”文件夹。

选择“新建作业”以打开“新建作业”窗口。

在“常规”选项卡中，输入作业名称，如“CopyTableToDB2”。

在“步骤”选项卡中，单击“新建”按钮以添加一个新的步骤。

在“新建作业步骤”窗口中，输入步骤名称，如“复制表格到DB2”。

在“类型”下拉列表中，选择“SQL Server集成服务(SSIS)包执行程序”。

在“常规”选项卡中，选择“包源”下拉列表中的“文件系统”。

单击“浏览”按钮，选择我们创建的存储过程“sp_CopyTableToDB2”所在的文件夹，然后选择该文件。

在“““级别”选项卡中，可以输入错误发生时的响应操作，还可以设置日志记录。

单击“确定”按钮，保存新的步骤。

在“计划”选项卡中，单击“新建”按钮以添加新计划。

在“新建作业计划”窗口中，输入计划名称、描述、频率、开始日期等信息。

单击“确定”按钮，保存新的计划。

回到“新建作业”窗口中，在“通知”选项卡中，可以选择作业运行成功或失败时要采取的操作。

单击“确定”按钮，保存新的作业。

现在，我们已经创建了一个作业，它将每隔一段时间（按计划）运行存储过程，将“db1”的“shared_table”表格中的数据复制到“db2”数据库的“shared_table”中。

3. 查询“shared_table”表格

我们可以通过查询“db1”和“db2”数据库的“shared_table”表格来验证是否成功地实现了共享。以下是查询“shared_table”表格的步骤。

-- 查询"db1"数据库中"shared_table"表格的内容
USE db1;
SELECT * FROM shared_table;
-- 查询"db2"数据库中"shared_table"表格的内容
USE db2;
SELECT * FROM shared_table;

上述代码中，我们在“db1”和“db2”数据库中查询了共享的“shared_table”表格的内容。如果两个数据库中都出现相同的行，则表明我们已经成功地实现了共享。