Export Data From One Database Table to Other Database

It is possible to transfer table data from one SQL Server database to another database easily using SQL Server Management Studio tool. In this SQL Server tutorial, I want to show how database developer and DBA can use SSMS tasks to copy data between databases on a SQL Server instance.

I have a database that I worked on temporarily named SAPTables and I want to move the database table dbo.SAPTables to a database where I will keep this table data permanently. Since I don't want to use the dbo schema for this new table on the target database, I created a new schema named saptbl

Now I can start the steps that will let me copy source table data into target database table.

First launch SQL Server Management Studio, SSMS and connect to the SQL Server instance where the source and target databases reside

Under Databases node on Object Explorer window, right click on the source database and follow context menu options: Tasks > Export Data...

SQL Server Import and Export Wizard is displayed

On next screen, SQL developer can choose the data source.
Select "SQL Server Native Client 11.0" as the data source.
If it is not filled automatically, type the SQL Server instance name, select the authentication method (Windows Authentication or SQL Server Authentication) and choose the database from available databases list.

Following screen enables the SQL developer to choose the destionation for the copied data.
Again select "SQL Server Native Client 11.0" as Destination.
Then type the SQL Server instance name. Since I'm transferring data from one database to other which reside on the same instance, I type the previous server name again here.

After choosing the Authentication method, select the target database name from the dropdown.
As well as selecting a database among existing databases, the DBA or SQL programmer can create a new database using the "New..." button after entering a name for the new database.

If you are lazy to type the name of the SQL Server instance, or if it is too long, you can execute following SELECT statement to get the server name from SQL engine.

The next screen provides a selection option for the data professional between "Copy data from one or more tables or views" or "Write a uery to specify the data to transfer"
I will choose the first option as seen in below screenshot.

Then we are ready to choose the source database tables or views that we will export their data to destination tables.
In below screenshot, you can realize that I've modified the default schema name dbo on Destination column to newly created schema saptbl.
Of course, you don't need to create a different schema and can use the default schema dbo instead.

Click Next button and mark "Run immediately" option to execute the task

Click next to review the selected actions then press Finish to execute the data export task

If you experience problems with tables, you can revisit Select Source Tables and Views to Edit Mappings. Edit Mappings screen will let the database developer to modify SQL codes for target tables, etc.