Read time 8 minutes
The need for copying tables from one database to another can appear in the circumstances like maintenance, testing, demonstration, migration, transfer to a different instance, and more. Doing so is easy and can be done via multiple ways as SQL Server offers several methods for the same.
People experienced with SQL queries can execute the same operation in SQL Server with no problems at all, but those who hold less knowledge regarding SQL queries and Transact-SQL may find the task difficult.
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.
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
This method makes utilization of SELECT INTO query.
Select * into TechForums20.userforum.user from TechForums19.userforum.user
Above query copies only the table schema and data, and in case if you wish to copy objects, indexes, triggers, and constraints then, doing so is not possible with SQL queries/commands. We’ll discuss later in this article that how can you copy the rest of the database items.
The second method for copying tables in SQL Server is the Export and Import wizard, available in SQL Server Management Studio. In this option, the user holds the choice either to import from the destination database or to export from the source database to transfer/copy the data.
Follow below steps to copy tables from one database to another in SQL Server:
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.
Note: Again, mention the Server name and authentication method in this step as well.
To make sure, that the tables you selected will be created in the destination database, click on the Edit Mappings button and tick the Create destination table option.
In case, if the tables contain the identity column, tick the Enable identity insert option, and click 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.
Note: Make sure to specify correct names of both Source & Destination databases, to transfer tables from one database to another.
Copying tables SQL Server using the SQL Server Management Studio is a quick way of completing the job, but it fails to transfer/copy the table’s indexes and keys.
And if you want to copy the table indexes & keys, you’ll have to make use of the Generate Scripts method.
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:
When you initiate any task or job associated with SQL Server database, there remains a slight risk of data loss or SQL Server database file corruption either due to wrong or incorrect execution or bad handling of the database file. There are critical situations that result in frustration as getting data back from the inaccessible MDF/NDF database file becomes almost impossible.
In scenarios of SQL Server Database file corruption or damage, we recommend using a SQL recovery tool .
This tool enables you to seamlessly, repair the corrupt, damaged, inaccessible MDF & NDF files with ease while maintaining the original file structure & 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.
It provides the user with full control over database objects and sets no restriction over the file size.
Additionally, we’ve made it compatible with a wide range of Microsoft SQL Server versions starting from SQL Server 2000 to 2019, so no matter what version of SQL Server you’re running on your system, it’ll be ready to use once the installation is finished.
Transferring or copying tables from one database to another in SQL Server is easy, but some methods are long and quite complex, and if something goes wrong during the process, both the data and the database file will be at high risk of file corruption & damage. To fight with such situations, try Kernel for SQL Database Recovery, the ace third-party SQL recovery tool for SQL administrators. It supports all SQL versions, including SQL Server 2019.