How to access my database from another server

Question

Hi Everyone,

DB: SQL Server 2008

I have two servers A and B. I want to able to insert data from server A into server B using a particular user. I can't seem to find a syntax for doing that. Can anyone please help me out on this.

Thanks

Answer 1

User [OpenQuery] function available in sql server

Answer 2

You can use sp_addlinkedserver and sp_addlinkedsrvlogin.

Answer 3

Assuming you already have the linked servers setup (sp_addlinkedserver and sp_addlinkedsrvlogin), the syntax would be like the following:

INSERT INTO ServerB.DatabaseName.SchemaName.TableName (value1, value2,..)  
SELECT value1, value2, ...  
FROM ServerA.DatabaseName.Schemaname.TableName  
WHERE Condition whatever here if needed

Answer 4

Use sp_AddLinkedServer as @Blorgbeard suggests. The script below should work if the tables have the same structure.

EXEC sp_addlinkedserver
 @server = 'A',
 @srvproduct = 'SQLServer OLEDB Provider',
 @provider = 'SQLOLEDB',
 @datasrc = 'whatever'

INSERT INTO B
SELECT * FROM A.myDb.dbo.MyTable