OPENDATASOURCE

vbnetcoder
vbnetcoder used Ask the Experts™
on
How would i use OPENDATASOURCE to insert the records from a table in one database table to an identically named table in another Sql server instance on another server?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
INSERT INTO yourTargetTable
SELECT *  
FROM OPENDATASOURCE( ...)
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Why use OpenDataSource? You do not want set up a linked server? In that case you'll need the correct connection string, as always. Assuming you need to use SQL Login instead of Windows Authentication:
insert into Tbl select * from OpenDataSource('SQLNCLI', 'Server=SourceServer\SourceInstance; User=sa; Password=sapwd').SourceDB.dbo.Tbl

Open in new window

Author

Commented:
I don't know all the details yet but i was told we maybe not be able to use linked server
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
So is the info above sufficient?

Author

Commented:
OLemo.  Likely, i will check it out latter today.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
#a41712687 is the answer, as far as we can tell. There is not much more to add to that.

Author

Commented:
ty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial