Insert into remote table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert into remote table

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 10:29:41

I want to transfer new and modified data [there is an Update Date column available] from a local table into an identical table on a remote server.

The intended process is to transfer first to a temporary [Staging] table (on the remote)

Then (at the remote server) pre-delete any rows, and insert all the data from the Staging table into the Master table

I want to do something like

SELECT *
INTO RemoteServer.RemoteDatabase.dbo.RemoteStaging
FROM LocalDatabase.dbo.LocalMaster

but that's not allowed (max. 2 part name on the INTO bit)

So I thought I'd create the table first using OPENQUERY or somesuch

SELECT @strSQL = '
SELECT *
FROM OPENQUERY(RemoteServer,
''
SELECT *
INTO RemoteDatabase.dbo.RemoteStaging
FROM RemoteDatabase.dbo.RemoteMaster
WHERE 1=0
'')
'

EXEC (@strSQL)

That gives:

The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=......]

Any ideas please?

Kristen

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-17 : 14:00:08

Your variant:
================================
DECLARE @strSQL nvarchar(1000)
SELECT @strSQL = '
SELECT *
FROM OPENQUERY(RemoteServer,
''
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
IF OBJECT_ID(''''RemoteDatabase.dbo.RemoteStaging'''', ''''U'''') > 0 DROP TABLE RemoteDatabase.dbo.RemoteStaging
SELECT *
INTO RemoteDatabase.dbo.RemoteStaging
FROM RemoteDatabase.dbo.RemoteMaster
WHERE 1=0
SELECT @@ERROR AS ErrorCode
'')
'
EXEC (@strSQL)
go

Alternative variant:
================================
EXEC sp_serveroption N'RemoteServer', N'rpc out', N'true'
go
DECLARE @strSQL nvarchar(1000)
SELECT @strSQL = '
IF OBJECT_ID(''RemoteDatabase.dbo.RemoteStaging'', ''U'') > 0 DROP TABLE RemoteDatabase.dbo.RemoteStaging
SELECT *
INTO RemoteDatabase.dbo.RemoteStaging
FROM RemoteDatabase.dbo.RemoteMaster
WHERE 1=0'
EXEC RemoteServer...sp_executesql @strSQL

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 14:19:14

"EXEC RemoteServer...sp_executesql @strSQL"

<thumps forehead>

Well knock me down with a feather.

I'm very grateful and if, as it looks like that's your first post, you registered especially to type that reply then I just don't know what to say!

Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources