Using OpenRowSet to insert data into remote table

[david.1982]

Hi Everyone,

I'm trying to figure out how to use OPENROWSET to copy data from a local database to a remote one. I am able to query data from the remote table, and update values without issue. Example to update:

update OPENROWSET('SQLOLEDB', 'www.xxx.yyy.xxx'; 'username'; 'password, 'SELECT * FROM dbname.dbo.tablename') set col=val where x=y

That works just fine, but here's what I need to get it to do:

• Delete all data in the remote table (not a problem)
• Insert all data from local table into the remote table, WITH identity insert.

The identity insert is the one that's throwing me off. If there is an easier way to do this, please let me know. Keep in mind this all needs to be done in SQL, not using management studio (if only it were that easy).

Am I missing something painfully obvious? Help me, SQL Gurus!