November 3, 2010 at 2:21 am
Hi,
Right now when we need to update values in a remote table in a Unidata database with values from a SQL Server table, we build the following string for each record, then execute it:
EXEC('UPDATE UniDataTable SET field = ''value'' WHERE key = number') AT LinkedServerName
That works fine for updating one record at a time and if there are only a few records. I know what it's doing and it's safe. However, when I have 200+ records I want to update, running an EXEC for each one can be time consuming and taxing to the system. It takes almost 5 seconds per update, and I think most of that is just the overhead of making that connection to the remote non-SQL Server database. I've also noticed that the query inside the EXEC() does not have all of the same built in SQL Server functions and syntax available to it that it would if it were not in the EXEC(). I mean, it supports IN and a few others, but not the whole gamut. I think that's because it's being executed on the remote machine, and may only support standard SQL. Anyway, is there a way to batch update rows using EXEC() without having to make a new connection for each row? If I were doing SQL Server table to SQL Server table, I would use something like the following:
UPDATE a
SET a.field = b.field
FROM UnidataTable a
INNER JOIN SQLTable b
ON a.key = b.key
but I need SQL table to Unidata tabe. So can I replicate this using one EXEC() AT RemoteServer statement?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 3, 2010 at 3:09 am
If you have the right permissions, you could create a temporary table on the target server, populate it with all the data you want to update and the use a single update statement on the remote server.
Something like this:
EXEC('CREATE TABLE TempTable (number int, value varchar(20)') AT LinkedServerName
INSERT INTO OPENQUERY(LinkedServerName, 'SELECT * FROM TempTable')
SELECT number, value
FROM MyLocalPermanentTable
EXEC('
UPDATE UniDataTable
SET field = (
SELECT value
FROM TempTable
WHERE UniDataTable.key = TempTable.number
)
') AT LinkedServerName
EXEC('DROP TABLE TempTable') AT LinkedServerName
It all comes down to knowing the exact sintax for a multiple-table update. The one I posted should work on any platform.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply