Thursday, August 26, 2010

Execute SQL on linked server (pass-through)

So living with one foot in Oracle land is helping me learn some of the lesser-known features of SQL.  For my 2nd post in one day here is another thing I found too good to not spread; executing SQL on a linked server withough SQL interpreting anything (a.k.a. Pass-Through).  While tinkering with using openquery to insert and update, delete was just too darn slow and I knew there had to be a better way.  Turns out the execute function allows you to specify a linked server as a target.  Too cool...

execute ('delete from table_name1' ) at <<linkedServer>>
execute ('delete from table_name2' ) at <<linkedServer>>
execute ('delete from table_name3' ) at <<linkedServer>>
execute ('delete from table_name4' ) at <<linkedServer>>
execute ('delete from table_name5' ) at <<linkedServer>>

In the case above there were foreign key references between the tables - in my example I knew the relationships (thus the order) to clean out the table to avoid FK errors - but with a little probing of the tables in oracle via openquery I'm sure you could produce the necessary deletes in the correct order.

Another way to do it (non pass through) is the following - which I recommend only for selects, as updates/inserts/deletes will be evaluated SQL server side and take too long.

select *
from openquery(<<linkedserver>>, 'select * from table_name1')

No comments:

Post a Comment