How to Transfer Table Data and Schema From One Database to Another
Do you want to know how to transfer table data and schema from one database to another in SQL Server in the most effective way? Keep Reading!
There are certain situations when Microsoft SQL Server user faces need to transfer table data from one database to another. It can be done via multiple ways as SQL Server offers various methods for the same.
Users who have knowledge of SQL Server and SQL queries can copy table data and schema without facing any problem. But users who hold less knowledge regarding SQL Server may find the task difficult.
Henceforth, in this blog we have mentioned manual and automated solution to perform the task efficiently. The automated solution is suitable for all the users, as it doesn’t require any technical knowledge. Users with a basic knowledge of SQL Server can easily copy table data from one database to another in just a few minutes.
So without wasting any time let’s get started with the solutions:
Methods to Transfer Table Data and Schema from One Database to Another
Suppose there are two databases-
Source – “databasetest1”
Destination – “databasetest2”
In source database “databasetest1”, there is a table named EMP containing data like Name, Address, City, and so on. To copy the EMP table data and schema from source database to destination database. You can try any of the method mentioned below:
Method #1. Transferring Table Data and Schema Using SELECT INTO Query
To transfer the EMP table data and schema from the databasetest1 to databasetest2, you can use the Select into SQL query. This statement will first create tables in the destination database and then it will copy the data and schema in these tables.
Use this query to copy database in SQL Server
Syntax:
Select * into DestinationDB.dbo.tablename from SourceDB.dbo.SourceTable
Example:
Select * into databasetest2.dbo.stu from databasetest1.dbo.emp
Note: If you want to copy other database objects such as indexes, triggers, and constraints, etc. then you need to use the second method Generate Scripts, prepare the script individually for this, after that you need to apply the script to the destination database.
Read More: How to Recover Table Data and Schema from Corrupt SQL Database?
Method #2. Copy Database in SQL Server Using Generate Scripts
This method is done by generating scripts and it will help users in moving not only the table data and schema but also views, functions, constraints, triggers, etc.
Go through the steps to generate a script to copy table schema:
1. Open SQL Server and right-click on the Source database name > Tasks > Generate Scripts.
2. Generate and Publish Scripts wizard will open then click on Next button.
3. In Choose objects page, select the Database objects you want to Generate Script for and click on Next.
4. In Set Scripting Options page, select the path to save the generated script file and click on Next.
5. Preview the summary window after selecting all the options then, click on Next.
6. Now, click on Finish to close the Generate and Publish Script wizard.
Method #3. Transfer Table Data From One Database to Another Using Export/Import Wizard
The next method to transfer table schema and data from one database to another database in SQL Server is- by using the Export and Import wizard that is available in SQL Server Management Studio.
To transfer or copy database in SQL Server follow the below steps:
Step 1. Open SQL Server Management Studio.
Step 2. Under the object explorer, right-click on the Source database > Tasks >Export Data.
Step 3. Import/Export Wizard will open and click on Next button.
Step 3. Now, specify the Server Name, Authentication method, Source database name, and click on the Next button.
Step 4. After that, specify the Destination Database Name, Server Name, Authentication method, and click on Next.
Step 5. Select Copy data from one or more tables or views and click on Next button.
Step 6. Select Source Tables and Views wizard will open, choose the Tables you want to transfer from source database to destination database, and click on Next.
Step 7. If you have selected more than one table to copy from Source database to destination database, then again click on Edit Mappings, and check for all the tables.
Step 8. Now, save and Run wizard will pop-up, click on the Next button to proceed.
Step 9. Finally hit the Finish button to close the wizard.
Limitations of manual methods
1. High chances of data loss or SQL Server database file corruption.
2. Lengthy and time-consuming process.
3. Requires technical expertise and granular knowledge.
4. Not suitable to transfer all database objects.
Method #4. Automated Way to Transfer Table Data From One Database to Another
If you need a quick and direct solution to copy table data and schema from one database to another database, then it is better to go with a reliable third-party tool i.e. SQL Server Data Migrator.
It is a brilliant utility to transfer table data with only Schema or Schema & Data both from one database to another database in SQL Server. Also, the software includes some advanced features that enable users to restore only one table in SQL Server to perform safe and successful data migration.
Every technical, as well as non-technical user, can use this solution without any hassle as it comprises a simple user interface. Also, the software is compatible with all versions of the Windows operating system (32-bit and 64-bit).
Now, follow the below-given steps to transfer table data and schema from one database to another:
Step-1. Download and Run the software in your system and click Open to add your database MDF file.
Step-2. Now, choose the Scan mode then select the SQL Server version of .mdf file and hit OK button.
Step-3. Preview the database, which you want to transfer and click on Export to start the migration process.
Step-4. Select export to SQL Server Database option. Software will fetch the available SQL Server Names. Select the name by clicking on the drop-down arrow and then select the Windows Authentication if you are using Windows Authentication Mode to access the database.
Step-5. Now, Software will fetch the databases and let you select the database components which you want to move.
Step-6. Select With Schema & Data option, it will copy schema and data of the table from one database to another database and then click on Export.
Finish! You have successfully transferred the table data from one database to another.
Wrapping Up:
Well, in the above blog, we have discussed different techniques to transfer table data and schema from one database to another database. Evidently. the described manual methods are somehow lengthy and may lead users to data loss and corruption. Thus, users can opt for an automated solution for quick and accurate results.