copy data from one server to another using query in import/export wizard?

I have one table a in one db server..and table b in another server.

I would like to update the table b using values in table a.

  • Difference between Implicit Conversion and Explicit Conversion in SQL Server
  • Self-Join with missing rows on both sides
  • SQL Server Freetext match - how do I sort by relevance
  • How Do You Monitor The CPU Utilization of a Process Using PowerShell?
  • Slow performance on LINQ query against SQL server
  • Is it possible to use MAX in update statement using sql?
  • How do I specify the server name in query ?

    UPDATE SELECT * from [db1].[dbo].StgMGBPData
       SET ASO_Ind = [db2].[dbo].Customer_Names.ASO_Ind,
           Company = [db2].[dbo].Customer_Names.Company,
           Rpt_Cust_ID = [db2].[dbo].Customer_Names.Rpt_Cust_ID
      FROM [db2].[dbo].Customer_Names
      JOIN [db1].[dbo].StgMGBPData ON [db2].[dbo].Customer_Names.Src_Cust_ID = [db1].[dbo].StgMGBPData.CustomerID

    2 Solutions collect form web for “copy data from one server to another using query in import/export wizard?”

    Try linked server because without linked server you can’t use server name in query.

    You can use sp_addlinkedserver. See an example here:

    EXEC sp_addlinkedserver
       @server = 'SERVER',-- your servername
       @provider = 'SQLOLEDB',
       @srvproduct = '',
       @datasrc = 'SERVER', -- your servername
       @catalog = 'NHS_032808' 
       -- your database name which you want to connect

    Like that exec the procedure with like that parameter after
    you can fire the query in connected database like below.

    select * from SERVER.NHS_032808.dbo.tbl_resident_mst

    If lack of permissions prevents you from using Linked Server yet you are able to connect to both servers from a client and have the necessary select-permissions on the table in server A and the necessary update/insert permissions on the table in server B, then you could use Micorosoft JET in your client to bridge the servers. Data from server A is pulled out to the client where it is then pushed to server B. This falls under the general category of “heterogeneous joins”, which was one of the major strengths of JET in an enterprise (multi-server) scenario. This bridging requires more bandwidth than a Linked Server approach does, but it will get the job done. You would create links in JET to the tables in each server and would thereafter be able to refer to both within the same query.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.