Sunday, January 8, 2012

T-SQL Trick

Leaned a new trick in T-SQL the other day and thought I’d post it here.

Turns out, you can use the Output clause from Insert/Update/Delete/Merge statements as a data-source for Insert-Select statements.

-----------------------------------------------------------

create table #T1 (
Act varchar(100),
Val int);

create table #T2 (
Col1 int);

insert into #T1 (Act, Val)
select ActM, ValM
from (
merge into #T2 as Tgt
using (select 1 as ColA) as Src
on Tgt.Col1 = Src.ColA
when not matched by target then insert (Col1) values (Src.ColA)
output $action, inserted.Col1) as MergeChanges (ActM, ValM);

select *
from #T1;

select *
from #T2;

-----------------------------------------------------------

The Output clause generates a dataset, which allows for the $action variable for Merge statements and so on, which can then be named (“as MergeChanges”) and given column names (“(ActM, ValM)”).  I used “ActM” to differentiate the “Action Merge” from the “Act” column of the #T1 table.

The uses for logging data changes are obvious.

Avoid the extra steps for Output Into, and bypasses the rules that Output Into is subject to on the characteristics of the target table.  It’s just an Insert-Select at that point, and subject only to those rules, which are much more usual.

This only works in Compatibility 100 (and I assume in Denali, which is probably 110, but I haven’t tried that yet).  Won’t work in databases with lower compatibility, even in SQL 2008 R2 servers.  They’ll let you use Merge, and even compatibility 80 (SQL 2000) will allow Output, but they won’t allow this particular trick.

Just picked this one up a few days ago and thought it was pretty cool.

Movie Review: Mission Impossible 4

Also recently saw Mission: Impossible - Ghost Protocol.  Goofy fun, lots of insane action.  Heard some people complain about it not being realistic.  I guess they missed the point, in my opinion.

Honestly, if you want a realistic movie about espionage/intelligence, you’re going to be sitting the theatre watching a movie of some government employee comparing photographs to each other, or listening to a radio broadcast in some language you probably don’t speak.  There’s a lot of paperwork involved.  Much of it involves things like following up on purchase orders and invoices.  There’s very little that would be at all entertaining.

James Bond, Mission Impossible, and all the rest, are fantasy fiction.  I enjoy them that way.

This one provided plenty of insane action, stunts, explosions, fights, sexy women (Léa Seydoux and Paula Patton are definitely attractive), and so on.  Pure eye-candy, from start to finish, for the whole movie.  Saw it in IMAX (refusing to see 3D movies these days; the headaches aren’t worth it), and it was worth the extra bucks.

8 of 10

Movie Review: Sherlock Holmes: A Game of Shadows

Recently saw Sherlock Holmes: A Game of Shadows.  Definitely liked it.  I can’t refute a number of idiotic review-claims I’ve read without spoiling scenes, so I won’t.  But some movie reviewers need to actually, I don’t know, read some of the books before they review the movie.  I think some of them are assuming the Basil Rathbone movies are “the original”, and may not ever have actually even heard of Arthur Conan Doyle.  (Like the idiot a few years ago who thought that The Two Towers as an The Empire Strikes Back rip-off, obviously not realizing that the Ring Trilogy was written about 40 years before the Star Wars material.)

Robert Downey Jr. does another great job as Holmes, and the rest of the cast carry their weight and then some.  Some of the special cinematography is spectacular, to say the least.

Definitely a fun movie.

7 of 10.