[Servers] No linked server? Need inserts? No problem!

Linked servers can be handy. But, also occasionally annoying, and not necessarily where you need them. Fortunately, OPENQUERY to the rescue. Yes, you can use it to do an adhoc query from another server. But, you can also use it to do an insert into another server.

Code:

INSERT INTO OPENROWSET('SQLNCLI', 'Server=yourserversname;Trusted_Connection=yes',
 'SELECT * FROM northwind.dbo.targettable') SELECT FieldA, FieldB, FieldC
FROM sourcetable 

2016/05/11 And here's the generic openrowset syntax you have to use to get SQL authentication.  Posting it here since I just blew 30 minutes trying to get it to work; despite what the documentation says, you have to use (at least on SQL 2012 SP3 querying SQL 2008 SP4)

SELECT * FROM 
OPENROWSET('SQLNCLI', 'Server=myserver;UID=mylogin;PWD=mypassword;',
 'select @@version')