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')

Composite Foreign Keys

You learn something new every day...  Today I found a table that nobody could figure out - turns out it's sole purpose in life is to maintain foreign key relationships (via data) so the objects knew how to verify referential integrity before inserting.  After a little googling I found out that it is possible to have a composite foreign key.  Crazy I thought...  but it works.  If anyone has any (negative) experience in this area I would love to hear about it.

(Note I am now living with one foot in SQL Server land and the other in Oracle Land - hence the semicolons)

create table tmp1 (
  f1  char(1),
  f2  char(1),
  primary key (
    f1, f2)
create table tmp2 (
  f1  char(1),
  f2  char(1)
  , constraint FK_composite foreign key (f1, f2) references tmp1 (f1, f2)

insert into tmp1 (f1, f2) values ('1', '1');
insert into tmp1 (f1, f2) values ('2', '2');

-- succeeds
insert into tmp2 (f1, f2) values ('1', '1');
insert into tmp2 (f1, f2) values ('2', '2');

-- fails
insert into tmp2 (f1, f2) values ('1', '2');
insert into tmp2 (f1, f2) values ('2', '1');

select * from tmp1;
select * from tmp2;

drop table tmp2;
drop table tmp1;