At times, as a DBA, users need to copy tables from one database to another in a similar instance or in an alternate SQL instance, for example, replicating explicit tables from a creation database to a DEV one for testing or troubleshooting purposes.
SQL Server offers a ton of techniques that can be utilized to play out the table’s data and outline copy procedure. So as to experience every single one of these techniques, we will consider the underneath situation:
Important Note: In case if you are facing SQL database corruption issue and you want to restore your crucial SQL Server database objects then the user can take the help of SysTools SQL Database Recovery Software. This software removes corruption issues from MDF and NDF files and also allows us to recover deleted database objects. This software supports Microsoft SQL Server 2019, 2017, 2016, 2014, 2012 and below versions. To know more the user can try the demo version of the software from here.
To copy tables, we need access to explicit databases – the source server and the destination server. Here,
Know-How to Copy Table from One Database to Another
Source Database: TechForums19
Destination Database: TechForums20
1.)Copy Tables Using Query
This strategy makes use of the SELECT INTO query.
Select * into TechForums20.userforum.user from TechForums19.userforum.user
The above query copies just the table schema and data, and on the off chance that you wish to copy objects, indexes, triggers, and constraints at that point, doing so is unimaginable with SQL queries/commands. We’ll talk about later in this article how might you copy the database objects.
2.) Utilizing SQL Server Management Studio
The strategy for copy tables in SQL Server is the Export and Import wizard, accessible in SQL Server Management Studio. In this choice, the client holds the decision either to import from the destination database or to export from the source database to move/copy the data.
Follow underneath steps to Copy Table from One Database to Another in SQL Server:
- Run SQL Server Management Studio (SSMS).
- Inside the Object Explorer, right-click on TechForums19 database and then go to Tasks > select the Export Data command.
- In this progression, determine the Server Name, Authentication method, and the Source database name, and click on the Next.
- In this progression, indicate the Destination database name and click on the Next.
- Select Copy data from one or more tables or views and click on the Next.
- Here, Select Source Tables and Views wizard will come on-screen; then select the Tables you need to copy from source database to destination database, as appeared underneath.
Note: To ensure that the table that you have chosen will be created in the destination place. After that click on Edit mappings option and select the create destination table option.
If tables have the identity column the check the Enable identity insert option and click on the OK button.
- In the wake of checking all the tables by means of Edit Mappings, click Next in the Select Source Tables and Views.
- Save and Run wizard would open in this progression, click on Next.
- Click on the Finish.
Note: Make sure to specify the correct names of both Source and Destination databases, to move tables starting with one database then onto the next.
Copy tables SQL Server using the SQL Server Management Studio is a snappy method of finishing the activity, however, it fails to move/copy the table’s indexes and keys.
Furthermore, in the event that you need to copy the table lists and keys, you’ll need to use the Generate Scripts strategy.
3) Utilizing Generate Scripts
This technique empowers you to copy the table diagram and information as well as items, records, triggers, limitations, keys, and so on.
Experience the means beneath to create a content to completely copy tables starting with one database then onto the next in SQL Server:
- Execute SQL Server.
- Right-click on the database name and then go to Tasks > Generate Scripts.
- Script Wizard would open, click on the Next
- Now, choose the Database you need to Generate Script for.
- Select the Object types.
- Select the tables to copy.
- Pick the Output choice for the script.
- Alter the database name with the name you need to execute the script for.
At the point when you start any project or occupation that is related to the SQL Server database, there stays a slight danger of data loss or SQL Server database file corruption either because of off-base or wrong execution or awful dealing with the database files. There are basic circumstances that bring about disappointment as getting data back from the inaccessible MDF/ NDF database file turns out to be practically inconceivable.
In situations of SQL Server Database file corruption or damage, we suggest utilizing an MS SQL Recovery tool. This tool furnishes the client with full control over database items and sets no limitation over the file size.
Furthermore, we’ve made it good with a wide scope of Microsoft SQL Server forms beginning from SQL Server 2000 to 2019, so regardless of what variant of SQL Server you’re running on your framework.
Also Read: How to Recover Data From SQL Server Transactional Logs.
Wrapping up
Moving or Copy Table from One Database to Another is simple, however, a few techniques are long and very mind-boggling, and if something turns out badly during the procedure, both the data and the database file will be at high danger of file corruption. In this manner, to battle with such circumstances SQL Recovery tool, the expert SQL recovery tool is accessible on the Internet