How to Copy Table Schema and Data from One Database to another Database in SQL Server
Table of contents
A few days ago, while migrating data from one database to another, I encountered an interesting problem which user faced a lot.
“I have two databases, I have to copy tables data from one database to another. My query is how can I copy table schema from one database to another with its schema and data”
In this blog, we will learn the easiest solution on how you can solve that query.
There are plenty of methods on how you can copy table schema and data from one database to another like Generate script wizard, Import/Export Wizard etc. But these methods are quite lengthy and requires users time.
The easiest solution to copy schema’s table from one database to another is to use SysTools SQL Server Database Migration Tool. You will able to migrate your tables schema by performing few steps.
How to Copy Table Schema and Data from One Database to Another?
- Launch SysTools SQL Server Database Migration Tool.
- Click Open and load your database MDF file.
- Choose the Scan Modes accordingly and select the SQL Server version of your .mdf file.
- Select the database table from the left side, which you want to migrate to another database.
- Click on Export for migration process.
- You will get two options for exporting. One is SQL Server Database and other is SQL Server Compatible Script. Choose it according to your need.
- After filling the Server credentials, Select the destination database as ‘Export to Existing database’
- Software will fetch the databases and let you select the database on which you want to move the selected table.
- You will get two options to export. Select ‘With Schema & Data’. This option will copy schema & data of your selected table from one database and export it to another and click Export.
- You will get popup of Export complete.
The blog covers the easiest workaround of mostly faced users query on how to copy schema & data from one database to another. You can also use Import/Export Wizard, Generate script wizard etc for object migration using SQL Server. But these methods has some limitations. In Import/Export wizard, the indexes and the key will not get transferred, you need to generate scripts for that. The export wizard fails if foreign keys connecting the tables together is not in a correct order. ‘Generate script’ method is used to generate a single script for tables schema, data, indexes and keys. But again, the limitation is this method doesn’t generate tables creation script in correct order.
So one can opt for SysTools SQL Database Migration Tool for fast migration of tables schema from one database to another.