Read time 8 minutes

Summary: This content discusses the need and methods for copying tables from one database to another in SQL Server. It outlines three methods: using SQL queries, SQL Server Management Studio’s Export and Import wizard, and generating scripts. It also briefly mentions SQL database file corruption and recommends a SQL recovery tool.

There are various situations where the requirement to transfer tables from one database to another arises, such as during maintenance, testing, demonstrations, migrations, or when moving data to a different instance. This task can be accomplished through several methods provided by SQL Server, making it a straightforward process.

Individuals proficient in SQL queries can effortlessly perform the same task within SQL Server. However, those with limited familiarity in SQL queries and Transact-SQL might encounter challenges when attempting the operation.

In this article, we’ll guide you through how to copy tables from one database to another in SQL Server using different methods available and discuss a little on SQL Server database file corruption and how to repair corrupt SQL database. SQL administrators will have to do all these tasks now or then to keep their databases healthy and to work always.

Instant Solution

Get Kernel for SQL Database recovery tool to repair corrupt or damaged MDF/NDF file. The software is also capable to preserve the original structure and properties of data.

To copy tables, we need access to specific databases – the source server and the destination server. Here,

Source Database: TechForums19
Destination Database: TechForums20

Copying Tables Using Query

This method makes utilization of SELECT INTO query.

Select * into TechForums20.userforum.user from TechForums19.userforum.user

The preceding query exclusively duplicates the table schema and data. If you desire to replicate objects, indexes, triggers, and constraints, accomplishing this task via SQL queries or commands is not feasible. In this article, we will delve into methods for copying the remaining components of the database.

Using SQL Server Management Studio

The second approach for duplicating tables in SQL Server involves using the Export and Import wizard, which is accessible within SQL Server Management Studio. With this option, users have the flexibility to either import data from the destination database or export data from the source database for the purpose of transferring or copying information.

Follow below steps to copy tables from one database to another in SQL Server:

  1. Open SQL Server Management Studio.
  2. Inside the object explorer, right-click on TechForums19 database > Tasks > select the Export Data command.
  3. In this step, specify the Server Name, Authentication method, and the Source database name, and click Next.
    select Source database name

    Note: For this guide, TechForums19 is the source database name as specified above. Change the source database name with yours and mention the server name & authentication method.

  4. In this step, specify the Destination database name and click Next.
    choose Destination database name

    Note: Again, mention the Server name and authentication method in this step as well.

  5. Select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  6. Here, the Select Source Tables and Views wizard will pop on-screen; select the Tables you want to copy from source database to destination database, as shown below, and click Next.
    Select Source Tables and Views

    Note:
    To ensure that the tables you’ve chosen are generated in the destination database, click the Edit Mappings button, and check the Create destination table option.

    If the tables include an identity column, check the “Enable identity insert” option, and then click the “Ok” button.

    If selected more than one table to copy to the destination database, click on Edit Mappings again, and check for all tables one after the other.

    Create destination table

  7. After checking all the tables via Edit Mappings, click Next in the Select Source Tables and Views.
  8. Save and Run wizard would open in this step; click on the Next button.
    Save and Run wizard
  9. Click Finish.
    complete the process

    Note: Make sure to specify correct names of both Source & Destination databases, to transfer tables from one database to another.

    successfully move the tables

    SQL Server Management Studio provides a speedy method for duplicating tables in SQL Server. However, it falls short when it comes to transferring or replicating the indexes and keys associated with the tables.

    If you intend to replicate the table indexes and keys, you will need to utilize the Generate Scripts method.

Using Generate Scripts

This method enables you to copy not only the table schema and data but also objects, indexes, trigger, constraints, keys, etc.

Go through the steps below to generate a script to fully copy tables from one database to another in SQL Server:

  1. Open SQL Server.
  2. Right-click on the database name > Tasks > Generate Scripts.
  3. Script Wizard would open, click on Next button.
  4. Select the Database you want to Generate Script for.
  5. Select the Object types and click on Next.
  6. Select the tables to copy.
  7. Choose the specific Output option for the script.
  8. Edit the database name with the name you want to execute the script for.
  9. Done.
What to do when the Database File Turns Corrupt or Damaged?

Whenever you undertake a task or job involving a SQL Server database, there exists a minor but potential risk of data loss or corruption of the SQL Server database files. This can occur due to incorrect execution or mishandling of the database files. In such critical scenarios, the frustration of retrieving data from inaccessible MDF/NDF database files can become nearly insurmountable.

In scenarios of SQL Server Database file corruption or damage, we recommend using a SQL recovery .

This tool empowers you to effortlessly restore corrupted, impaired, or inaccessible MDF and NDF files, all the while preserving their original file structure and properties. It also allows you to recover MDF files after a ransomware attack. With this tool, you can recover SQL all objects like tables, triggers, functions, rules, deleted records, etc.

SQL database recovery tool

It provides the user with full control over database objects and sets no restriction over the file size.

Furthermore, we have ensured compatibility with a broad spectrum of Microsoft SQL Server editions, spanning from SQL Server 2000 to 2019. This means that regardless of the specific SQL Server version running on your system, our product will be readily available for use upon completing the installation.

Wrap

Migrating or duplicating tables from one SQL Server database to another can be a straightforward task, but certain approaches can be time-consuming and intricate. Moreover, in case of any mishap during the operation, both the data and the database file become susceptible to potential corruption and harm. To address these challenges effectively, consider employing Kernel for SQL Database Recovery, a leading third-party SQL recovery tool designed for SQL administrators. This tool offers compatibility with all SQL versions, including SQL Server 2019, ensuring comprehensive support for your database recovery needs.

Kernel for SQL Database Recovery