Login to another server from Current server using Server Authentication

Question

I googled a lot but did'nt find clear solution
I am trying to insert data from a

table1 of a database1 from server1  

to

table2 of database2 from server2  

where i am unable to login to server2 because of sqlserver authentication

1. So my question is how to connect to server2 using server authentication from server1 ??
2. Also give me simple solution to insert data from one server to another ??

Answer 1

You can use OPENROWSET something like this ...

USE TargetDatabase
GO

CREATE TABLE #TempTable1(CompanyName NVARCHAR(100), City NVARCHAR(100), Country NVARCHAR(100))
GO

INSERT INTO #TempTable1(CompanyName , City , Country)
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=[SourceServer\InstanceName];Trusted_Connection=yes;',
                'SELECT CompanyName, City, Country
                 FROM Database.dbo.TableName') AS a;
GO