copy data from one server database table to another server database table in SQL server

How to copy data from one server database table to another server database table in SQL server

Introduction

Here I will explain how to copy data from one server database table to another server database table in SQL server.

Background

I created one table Addresstype in database1 in server1 with some data. I have created same tableAddresstype with same columns in database2 in server2. Now I want to fill AddressTpe table inserver2 with data of Addresstype in server1.

In this situation we need to use SSIS (SQL Server Integration Services) to complete our work quickly.

Steps Description

      1). Open your SQL Server Management Studio and connect to your server here I am connecting server2

     2). After that select your database (where ever your table exists select that databse) from your server.

     3). Right click on your selected Database go to Tasks under that go to Import data one wizard will open that SQl Server Import and Export Wizard

      See these steps in action

After that click next in wizard 

        1).In next wizard select Data Source as Microsoft  OLEDB Provider for SQL Server from dropdown

 

        2).After that enter your Datasource server name here I am entering server1 because I want to get data from table in server1

        3).After that select database in that server here I am selecting databse1 because my table in this database and click next

       4). Here you need to repeat the same steps from 1 to 3 but here you need to enter destination server details i.e. the server wherever we need to save the data here I am entering server2 and selectingdatabase2 after that click next

     5). By default select option copy data from one or more tables or views and click next here you need to select the tables from whichever tables data you need to copy after that click next and after that click finish wait sometime it will display the result .

     See these steps in action check from first part continuation onwards this image will run continuously that's why i am saying check this image from first wizard next onwards then it will clear for you 

     After see your table that filled with data from another server