Copy tables between databases in SQL Server using Import-and-Export Wizard

Introduction to Import-and-Export Wizard : The wizard allows easy steps through a process, and to execute the data copy process with writing very little or no code. However, for importing and exporting data from one source into another, the below steps could be followed -

1. Open the Object Explorer, select the database, right-click on the database name, and select Tasks and choose Export Data... option.


2. Now choose the data source, there are different sources that can be used. i.e., SQL Native Client 11.0, Select Server name and database name.


3. Now choose a destination Server name and Database and click Next.


4. Select the objects to copy data from table\view to the destination or write a query to data transfer and Click Next.


5. Choose Run immediately or Save SSIS Package and Click Next.


6. Summary of the actions that will be performed using the wizard will appear. Click Finish to execute the job steps.

After the successful finish of the wizard, the tables will be visible in the destination database.

Benefits of using Import-and-Export Wizard : To copy the objects and the content of specific tables from a database to another database in the same instance or in a different SQL instance, for example: copying specific tables from a production database to a development database for testing or troubleshooting issues. Limitation of using Import-and-Export Wizard : The copy is dependent on factors like the number of tables, size, and current available space in the database. If the total size of the tables is more than 50% of the total size of the database than the Import-and-Export Wizard is time-consuming.