Tables in the database are the entities that hold the records of items and persons. End-users execute queries on these tables to get the result. So, SQL tables may be required by developers for testing, migrating table content to another database. So, Database Administrators must be aware of the methods on how to copy tables from one database to another. Here we will know some of the methods which can be used to move SQL tables.
Top 3 Methods to Copy Table in SQL
- By SQL Query
- By SQL Server Management Studio
- SQL Scripts
SQL Query method is best for users who know the technical terms. This method is fast and easy
Transfer SQL Tables via SQL Query
Assuming Source database as Dbase2018 and Destination database as Dbase2019 execute the query
- Open the SQL console
- Write the query
- Select *into Dbase2019.userforum.user from Dbase2018.userforum.user
This will copy the SQL schema and table contents from Dbase2018 to Dbase2019. If you are not comfortable with SQL queries then use the SQL management studio.
Copy SQL Tables Using SQL Server Management Studio
- Start the SQL Server Management Studio
- In the Explorer window, select Dbase2018 database
- Right click on the database, select Tasks and choose Export Data option
- A new window will open select the data source and server name
- Choose authentication method and database as Dbase2018, Next
- Enter the destination database details, select options and enter database name as Dbase2019
- You will be directed to a new window, click on ‘Copy data from one or more tables or views’
- Select the Tables and views to copy to other database and then Edit Mappings
- In column mappings section choose ‘create destination table’
- Check all the tables that are selected for transfer and click OK,
- In the Select Source and Tables window, click Next and you will be directed to Save and Run Package
- Checkmark the option ‘Run Immediately’ and then Next
- Check information and then Finish
- The tables will be moved from Dbase2018 to Dbase2019
- A new window appears with message ‘the execution was successful’, click close.
This is the easiest way to copy table from one database to another in SQL
Copy Tables By Generating Scripts
- Open the SQL Server Management Studio and right click on the source database
- Select Tasks and then ‘Generate Scripts’
- In next window, choose ‘Select specific database objects’ option
- Choose the tables to copy and then Next
- Set Scripting options as Single File, Unicode Text and enter the path to store generated script
- Click on the ‘Advanced’ button
- Under Types of data to script click only on ‘Schema and Data’
- Click Ok, then next in Summary section view details and Next
- The scripts will be saved, click on Finish
- So, this is another method to copy tables in SQL.
Database Administrators can choose any of the above methods they find fit for the migration. With Script method user can also copy the indexes, triggers, and other objects of the database.