load data from one DB in a server to another DB in another server

[cuqsy0]

I am trying to do the following data loading.

Scenario: I have an login account UA, password UA123 in sql server A. I also have an login account UB, password UB123 in sql server B.

In server A, there is a table TableA (COL1 INT, COL2 VARCHAR(20)) in database DBA. In server B, there is a table TableB(COL2 VARCHAR(20)) in database DBB.

Now assuming I login on server B, and wish to insert the COL2 of TableA into TableB. How to write a SQL or T-SQL for this task?

Thanks in advance.

CUQ

[Rabbit]

If you have a linked server, you just need to qualify it.

SELECT *
FROM linkServer.dbName.schemaName.tableName

If you don't have a linked server, you can use an OPENDATASOURCE.

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).dbName.schemaName.tableName

[cuqsy0]

@Rabbit
Thanks for the reply. I like most your second method, and try to use it. However, it seems there is a little problem.

Here is my test result using SQL Server 2012. Instead of using two servers, I run the query on one server, and with different user accounts. I set up two user accounts (UA and UB) on the same SQL server "localhost". UA account has full access to AdventureWorks2012, and UB account has no access to AdventureWorks2012. I logon as admin and did two steps. First I did a reconfigure, and it was sucessful. Second, I tried to SELECT. If I use admin account, the select is successful, but it has no practical meaning because admin already has access to AdventureWorks2012. If I logon UB and tried to SELECT, I got an error:

-------------------------------------------------
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO
-------------------------------------------------
SELECT * FROM OPENDATASOURCE
('SQLOLEDB', 'Data Source = localhost; User ID = UA; Password = UA123 ' )
.[AdventureWorks2012]
.[HumanResources]
.[Department]
-------------------------------------------------
The error message is "Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists." Any idea?

--CUQ

[cuqsy0]

@Rabbit
Finally I choose your method one. Here is my result and it works fine.


--------------------------------

USE master;
GO
 
-- The remote server name is assumed to be 'RemoteServerIPAddress\sqlexpress,1433'
 
EXEC sp_addlinkedserver 
   N'RemoteServerIPAddress\sqlexpress,1433',
   N'SQL Server';
 
EXEC master.dbo.sp_addlinkedsrvlogin  @rmtsrvname =  'RemoteServerIPAddress\sqlexpress,1433' 
      ,  @useself =  'FALSE'  
      ,  @locallogin =  'UB'  
      ,  @rmtuser =  'UA'  
      ,  @rmtpassword =  'UA123'
 
EXEC sp_serveroption @server =  'RemoteServerIPAddress\sqlexpress,1433' 
      ,@optname =  'connect timeout'     
      ,@optvalue =  '60' 
 
------
USE AdventureWorksDW2012
 
CREATE TABLE TABLEB(COL2 VARCHAR(255))
 
INSERT INTO TABLEB(COL2) 
  SELECT AccountType 
    FROM [RemoteServerIPAddress\sqlexpress,1433].[AdventureWorksDW2012].[dbo].[DimAccount] 
 
GO
 

--------------------------

Thanks a lot!

--CUQ