The migration of data from one database to another is a regular job performed by a Database Administrator. This scenario frequently occurs when businesses need to integrate data from several sources, migrate data to a data warehouse, or do data analytics on a collection of data held in various databases. Ingesting data from one table in a database to another table in a different database is a common scenario, especially when both tables have identical columns. with this article, we'll go over how to do this efficiently with SQL Server Management Studio.

Create the Database Connection
Before you can transfer data between tables in different databases, you must first ensure that you have the required permissions and connectivity to both databases, which frequently entails defining connection settings such as server addresses, authentication credentials, and database names.

Recognize the Tables
It is critical that you have a thorough understanding of both the source and destination tables. The tables in our case share the same columns, which simplifies the operation. You should still double-check that the column names, data types, and constraints are consistent between the two tables. Any inconsistencies can cause data integrity difficulties during the transmission.

Create and run the SQL query
To transfer the data, create a SQL query with the INSERT INTO... function. SELECT the typically used statement for that purpose. The source table, the fields to be transferred, and the destination table should all be specified in the query. After you've written the SQL query, click the Execute button to run it. In our case, we wish to move the data from the SalesData database's DimCustomer table to the data warehouse database's DimCustomer table. The table structure in both tables is the same.

We wrote a simple query in the screenshot below to retrieve all of the records from that table. 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.

SELECT * FROM DimensionCustomer;

In the following snapshot, we went to the destination database, DataWarehouse, and selected all of the columns in the dimCustomer table with no data ingested thus far. This is required to validate the destination table's structure.

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;

HostForLIFEASP.NET SQL Server 2022 Hosting