Delete using OPENQUERY

• NigelFN

  Default port

  Points: 1467

  More actions

  August 13, 2001 at 9:15 am

  #77226

  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.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737868

  More actions

  August 13, 2001 at 10:47 am

  #417586

  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

  steve@dkranch.net

• jjradha

  SSChasing Mays

  Points: 617

  More actions

  July 24, 2008 at 10:28 am

  #847928

  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