Transferring Data from One Table to Another Across Databases

Introduction

One common task that a Database Administrator performs is the movement of data from one database to another. This scenario often arises when organizations need to consolidate data from multiple sources, migrate data to a data warehouse, or perform data analytics on a set of data stored in different databases. A classic scenario is ingesting data from one table in a database to another table in a different database, especially when both tables have identical columns. In this article, we'll explore the steps to accomplish this task effectively in SQL Server Management Studio

Establish the Database Connection

Before you can transfer data between tables in different databases, you must ensure that you have the necessary permissions and connectivity to access both databases, which often involves configuring connection settings, including server addresses, authentication credentials, and database names. 

Understand the Tables

It is important to ensure that you have a clear understanding of both the source and destination tables. In our case, the tables have the same columns, which simplifies the process. However, you should still verify that the column names, data types, and constraints match between the two tables. Any discrepancies can lead to data integrity issues during the transfer.

Write & Execute the SQL Query

To perform the data transfer, you'll need to write the SQL query by using the INSERT INTO ... SELECT the statement, which is commonly used for that purpose. The query should specify the source table, the columns to be transferred, and the destination table. Once you've written the SQL query, execute it by clicking on the Execute button. In our example, we want to transport the data in the DimCustomer table of the SalesData database into the DimCustomer table in the data warehouse database. Both tables have the same table structure.

In the screenshot below, we wrote a simple query to fetch all the records from that table.

SELECT * FROM DimensionCustomer;

In the next screenshot below, we switched to the destination database: DataWarehouse, and we selected all the columns in the dimCustomer table without any data ingested thus far. This is necessary to verify the structure of the destination table.

In the next screenshot below, we wrote and executed the query, and from the message displayed, a total of 18,484 records were inserted into the destination table.

INSERT INTO DataWarehouse.dbo.DimCustomer (
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
)
SELECT
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
FROM SalesData.dbo.DimensionCustomer;

Verify the Results

After the query execution is complete, it's essential to verify the results. To verify, we executed a simple below mentioned query, and all the records were returned.

SELECT * FROM dimCustomer;