Classic update if exists, otherwise insert with a twist

Question

Classic update if exists, otherwise insert with a twist

Chrissy321

SSCoach

Points: 15763
More actions
October 18, 2011 at 9:09 am

#250297

Hello All,
The twist is I am updating a linked server and I have learned that the MERGE statement as well as the OUTPUT clause can't be used against linked tables.
I am resigned to not using MERGE but I really need the functionality of the OUTPUT clause. At a minimum I want to log when an INSERT or UPDATE happens and log the new data as well.
Since OUTPUT was new in 2005 I assume there are techniques that will allow me to capture the updated or inserted records. If any one can provide links or even a general overview of such a process I would be very appreciative.
Thanks
IF EXISTS
(SELECT....my criteria)
BEGIN
--Do the update
END
ELSE
BEGIN
--Do the insert
END

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

You could always try using the Upsert method. That is Update first and check if @@rowcount is zero. If it is 0 then do the insert. This will help if you are doing more updates than inserts. This means that you do not need to use IF EXISTS

-Roy

Jared SSC Guru Points: 61793 More actions · Answer 2

I can't find any documentation about MERGE not working on linked server... However, if it is not working from the source side I would try running it on the destination server.

Thanks,

Jared

Jared
CE - Microsoft

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Merge I do not think will work with LinkedServer. Thats what I know

-Roy

Chrissy321 SSCoach Points: 15763 More actions · Answer 4

http://msdn.microsoft.com/en-us/library/bb510625.aspx

target_table cannot be a remote table

I have tested this and it seems so.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

Is there anything stopping you creating a stored procedure on the linked server and calling that remotely?

RPC calls perform much better.

Jared SSC Guru Points: 61793 More actions · Answer 6

"<table_source> can be a remote table or a derived table that accesses remote tables."

Why not create the procedure on the target? Would run much better too...

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 7

MysteryJimbo (10/18/2011)
Is there anything stopping you creating a stored procedure on the linked server and calling that remotely?
RPC calls perform much better.

🙂

Jared
CE - Microsoft

Chrissy321 SSCoach Points: 15763 More actions · Answer 8

I can consider that but its probably not the best solution in my situation

-There are networking restrictions between the source and target. The target doesn't know the source exists.

-I'd like the remote server to be a repository of data not a pleace where processing is going on

So my current model is that processing takes place on my source server. The source server can issue Select, Update, Insert and Delete statements against the target server. The target server is a hardened repository of data with limited capabilities. I don't want to add any complexity to my target server.

So I am going to need to do an Update/Insert. That's not too difficult. Capturing and logging the specific updates and inserts looks to be the more challenging task since its essentially looks like an auditing task.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

don't give up quite yet. You can STILL use the output, you just have to use the output as a derived table. Something like this:

insert bob2(col1)

select * from

(INSERT INTO remote.dbname.usrname.MyTable(Col1)

output inserted.Col1

SELECT 'blah' UNION ALL

SELECT 'blahblah' UNION ALL

SELECT 'blahblahblah' ) k

so your OUTPUT is basically a SELECT, not an INSERT.just wrap the remote insert and you should be fine.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chrissy321 SSCoach Points: 15763 More actions · Answer 10

Getting

"A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement."

INSERT INTO DataUpdateCapture (mydate)

SELECT * FROM

(

INSERT INTO MyLinkedServer.MyDatabase.MySchema.MyTable(mydate)

OUTPUT INSERTED.mydate

SELECTgetdate()

) x

The insert works fine by its self.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Chrissy321 (10/18/2011)
Getting
"A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement."
INSERT INTO DataUpdateCapture (mydate)
SELECT * FROM
(
INSERT INTO MyLinkedServer.MyDatabase.MySchema.MyTable(mydate)
OUTPUT INSERTED.mydate
SELECTgetdate()
) x
The insert works fine by its self.

ugh - that's a nasty wrinkle. sorry about that.

Hmm - the next option would be to run it in an OPENQUERY, and the insert outside, but that's about the same issue as why you don't want to have stored procedures.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?