August 13, 2001 at 9:15 am
I have an application where I need to delete records from a remote table that is accessed via OPENQUERY.
I understand that you can INSERT data into a remote table but I wanted to DELETE the data based on a query that must run on the remote server.
I tried using a standard DELETE statement to reference the Remote table, but that took too long (table-scanning).
I then tried a few variations on using OPENQUERY with no success.
Since then, I have solved the problem by writing a parameterised procedure on the Remote server and executing that remotely.
Can anyone shed any light on how else it may be done, efficiently. I may not always have the luxury of being able to create a procedure on the Remote Server.
Cheers,
Nigel.
August 13, 2001 at 10:47 am
What type of remote server? Different providers support different types of operations.
You should be able to add this item as a linked server and perform a delete against it. If you do not want to add a linked server, then look at this script:
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries) a
insert OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries)
select 'United States', 'US'
insert OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries)
select 'Canada', 'CN'
insert OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries)
select 'Mexico', 'MX'
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries) a
delete OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\countries.mdb';'admin';'', countries)
where countryname = 'US'
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'SSC Test',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\countries.mdb'
GO
SELECT *
FROM OPENQUERY([SSC Test], 'SELECT countryname FROM countries')
insert OPENQUERY([SSC Test], 'select * from countries') select 'Bermuda', 'BM'
SELECT *
FROM OPENQUERY([SSC Test], 'SELECT countryname FROM countries')
delete OPENQUERY([SSC Test], 'select * from countries') where countryname = 'MX'
SELECT *
FROM OPENQUERY([SSC Test], 'SELECT * FROM countries')
GO
exec sp_dropserver @server='SSC Test'
I built a simple Access db with a single table, 2 fields.
Steve Jones
July 24, 2008 at 10:28 am
delete from [webinterim1].[interim_KRISHNA].[dbo].[sales2]
insert into [webinterim1].[interim_KRISHNA].[dbo].[sales2]
select top 10 *
from [MM-SQL-002].webdb.dbo.SALES
webinterim1 = server (linked server)
interim_KRISHNA = database
MM-SQL-002 = LOCAL SERVER
webdb = DATABASE
sales2 = table
hope this helps.
Marcello Miorelli
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply