SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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

SQL Server Import and Export Wizard

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.

choose data source for SQL Server Data Export task

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.

destination for SQL Server Data Export Task

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.

table copy or query for SQL Server Import and Export tool

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.

map source database tables to destionation tables

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

save and run SSIS package for SQL Server data export

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

copy data from one database to other on SQL Server

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.