When working with SQL Server, it is common to have data spread across multiple databases or servers. In some cases, you may need to combine data from these different sources into a single table for analysis or reporting purposes. In this article, we will explore how to achieve this using SQL Server.

Scenario

Let’s consider a scenario where we have a Sales Database that stores data for different years. Each year’s data is stored in a separate database on a different server. For example, we have SalesDB_2010 on Server1, SalesDB_2011 on Server2, and SalesDB on Server3 (which holds the current or most recent data).

Sample Data Preparation

Before we proceed, let’s prepare some sample data for this demonstration. We will create a table called FactSales in each of the three databases using the following script:

CREATE TABLE dbo.FactSales(
   Id INT IDENTITY(1,1) PRIMARY KEY,
   DateKey INT,
   ProductKey INT,
   SalesAmount MONEY
)

Next, we will insert some sample data into each of the databases:

INSERT INTO dbo.FactSales (DateKey,ProductKey,SalesAmount)
VALUES
   (20100101,123,1000.26),
   (20100201,456,1003.20),
   (20100301,789,1007.856),
   ...

Combining Data from Multiple Databases

To combine data from multiple databases into a single table, we can use the UNION ALL operator. Here are the steps to achieve this:

1. Create linked servers to the databases that hold the data you want to combine. In our scenario, we would create linked servers “SalesDB_2010” pointing to Server1 and “SalesDB_2011” pointing to Server2.
2. Open SQL Server Management Studio (SSMS) and connect to Server3, which holds the current data.
3. Open a new query window and paste the following query:

SELECT Id, DateKey, ProductKey, SalesAmount 
FROM OPENQUERY(
   SalesDB_2010,
   'SELECT Id, DateKey, ProductKey, SalesAmount FROM [SalesDB_2010].[dbo].[FactSales]'
)
UNION ALL
SELECT Id, DateKey, ProductKey, SalesAmount 
FROM OPENQUERY(
   SalesDB_2011,
   'SELECT Id, DateKey, ProductKey, SalesAmount FROM [SalesDB_2011].[dbo].[FactSales]'
)
UNION ALL
SELECT Id, DateKey, ProductKey, SalesAmount 
FROM [dbo].[FactSales]

Make sure to replace “SalesDB_2010” and “SalesDB_2011” with the actual linked server names you created. This query retrieves data from each database and combines it using the UNION ALL operator.

Execute the query to see the combined data from all the databases in a single result set.

Conclusion

Combining data from multiple databases into a single table in SQL Server is a straightforward process. By using linked servers and the UNION ALL operator, you can easily retrieve and consolidate data from different sources for further analysis or reporting.