In a SQL Server database, this article shows how to replicate a table containing data between databases. This article uses a source database that has a table with data in it and creates a new database that has a copy of the source database's table with data in it. This article outlines a few easy ways to accomplish this. Let's now examine a real-world example of how to transfer a table containing data between databases in SQL Server. The SQL Server Management Studio is used to create the example on SQL Server.Creating a table in SQL Server

Now we create a table named employee using.
CREATE TABLE [dbo].[Employee]
(
    [EmpID] [int] NULL,
    [EmpName] [varchar](30) NULL,
    [EmpSalary] [int] NULL
)

The following is the sample data for the employee table.

Method 1. Copy Table using SELECT INTO
This command only copies a table's schema and its data. The Select into is used to copy a table with data from one database to another database's table. The Select into is also used to create a new table in another database. The general syntax to do that is:

Syntax
SELECT * INTO DestinationDB.dbo.tableName FROM SourceDB.dbo.SourceTable

Example
The employee table is defined in the master database. It is also called the source database. Now you want to copy the table with data from the master database to the model database. The following query defines it:
SELECT * INTO Model.dbo.[Employee] FROM Master.dbo.[Employee]

Now hit F5 to execute it.

Now press F8 to open the Object Explorer and select the model database to see the employee table.

Method 2. Generating Script in SQL Server
If you want to copy all objects, indexes, triggers, constraints, etc., do it using "Generate Scripts...". Suppose we have a database named Test. Now right-click on the Test database and select the "Generate Scripts..." option.

database Name -> "Tasks" -> "Generate Scripts...."

Now click on "Generate Scripts...". The Generate Scripts wizard will be opened.

Now click the "Next" Button and select tables and Stored Procedures.

Now click on the "Next" Button and provide the proper name with the file's path.

Now click the "Next" Button and review your source and target location.

Now click on the "Next" Button.

Now finally, click on the "Finish" button. The script file has been generated for the Test database. To see the generated script file, select the file's location on your computer.

Creating a Database in SQL Server
These are the following steps to create a new database:

• Press F8 to open the Object Browser in SQL Server Management Studio and expand it
• Database -> right-click-> select New database
• This would open the "New Database" window
• Now enter a database name to create a database
• Now click on the OK button to create the database. The new database will be shown in the Object Explorer

Currently, the database, named DatabaseTest, has been created.

Copy Database Schema and Data to Other Database
Now right-click on the script file, open it in Notepad, copy all the data, and paste it into the query window in SQL Server. It will look as in the following.

Now only change the database name test to DatabaseTest.

Now press F5 to execute the script and expand the databaseTest to see the schema and data.

Summary
This article taught us how to Copy Table With Data From One Database to Another in SQL Server.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.