The only way to keep from rolling back the record inserted by the trigger, is to do it with another connection. 

SP_OA could be used to create such a connection...

...and, still, if you have FULL RECOVERY set, none of that will help the LOG file because all of the updates will be recorded whether they be single updates, batches,...

JUST in case you need it as a single query (In the absence of CTE's, I do like the temp table solution better which is why I didn't answer previously...

Yes, it does make sense... What I'd recommend is writing a script that would populate a working table (permanent table that will last until this problem is resolved and then...

The update isn't really ignored if there's no match... it just doesn't update anything although it is executed.

Try adding the -c parameter.  If that doesn't do it for you, add the -C parameter with the proper code page (see Books Online for the proper code page.)  Do...

Yes... you can insert the output of a SELECT into a temp table with an IDENTITY column to keep track of which rownumber you are working on.  Then, loop through the...

I think you have it... the only thing that I would change is UNION to UNION ALL since it's not likely that there will be any overlap between the two...

Johann,

Since you're new, the error at lines 24 and 42 was because you cannot have a WHERE clause in an INSERT/VALUE statement.  Sreejith corrected that by changing it to an INSERT/SELECT.

The...

Take a look at Books Online for the OSQL command... the problem is that the default output is set to 80 characters and you must invoke the "-w" parameter with...

I gotta ask the question, again... you do realize that, except for the Developer's Edition, SQL Server must be installed on a Windows Server...?  You've not said that you are actually...

You are not wrong.  Conversely, if you don't want the IDENTITY column to be reset to the SEED value, you must use DELETE, instead.

In that case, why not just update by what's in the MediaID column?  Load the unique media IDs into a temp table (with an autonumbering IDENTITY column) and step through...

Would you post your "adaptation" please?

I suppose there's a way... but, instead, I normally create a temp table with the results of the called proc and then just join to the temp table.

Create the temp...