26 Jun

Just on the Border of Your Waking Mind…

On what planet was Transact-SQL written?

Don’t let your eyes glaze over if your are not used to SQL statements, I’ll explain how silly it is later. For the nonce, however, try this on for size:

USE MyDatabase
IF NOT EXISTS(SELECT * FROM MonthlyInfo WHERE GeneratedDate = (SELECT CONVERT(varchar,GETDATE(),101)))
INSERT INTO MonthlyInfo (GeneratedDate) values (CONVERT(varchar,GETDATE(),101))
DECLARE @MonthlyInfoID int
SET @MonthlyInfoID = (SELECT MonthlyInfoID FROM MonthlyInfo WHERE GeneratedDate = (SELECT CONVERT(varchar,GETDATE(),101)))
INSERT INTO RestoreMonthlyDetails (ServerID,MonthlyInfoID,RestoreResultID) SELECT ServerID,@MonthlyInfoID,’5′ FROM ServersByActiveAgreements WHERE BackupApplicationID != ‘3’
INSERT INTO VulnScanMonthlyDetails (ServerID,MonthlyInfoID,VulnScanResultID) SELECT ServerID,@MonthlyInfoID,’1′ FROM ServersByActiveAgreements

Alright, I am by no means claiming to be the SQL guru…so I’m sure that it must make sense to SOMEONE out there. But would you just LOOK at the last Insert statement?

Do a Google and you will see syntax that seems to make perfect sense. As nearly as I can tell, you tell it you are intending to an INSERT into a certain table, and then in parenthesis you tell it the column names inside that table you want to update.

So… INSERT INTO tablename (column1,column2,etc)

That’s great. Perfectly understandable. In simple statements, it is followed by the VALUES argument and in the most simplistic, a list of values that match the column names.

Such as INSERT INTO tablename (column1,column2) VALUES (1,Bob)

And now there is a new record inside table name, where column1 has the value of “1” and column2 has the value of “Bob”. We’re fine so far. But what happens when you want to update or add more than one record at a time? Well, you can put a SELECT in the place of VALUES and make a “sub-query” to find the values you happen to need. Sure made sense to me when countless websites told me to do just that.

The only problem is that it doesn’t WORK!!!

If the select has a comparison in it, like = > < and so on, you cannot return more than one value or you still get an error. And so it went with me last Friday for about 2 hours. I tried about every combination I could imagine. It was at this point of utter frustration that Christian Weidelman walked up to me and said, “Oh, take out all those parens…no, no, just list the values names and put the FROM at the end.” Like it was no big deal.

Ran like a champ. I HATE that. 🙂

How does it magically know which argument the FROM is modifying??!?!?! This is IDIOTIC. Or I am. Or Something!

I mean, look: SELECT ServerID,@MonthlyInfoID,’1′ FROM ServersByActiveAgreements

I have the ServerID which is actually the argument that the FROM works against. However, in between the two I have a variable (@MonthlyID) and a constant (1). Why isn’t the FROM trying to figure out what I mean with the number 1? How does it know?!?!?!

Also, my sister-in-law was walking quietly down the street in Thailand with another young woman. They were dressed nicely, and didn’t happen to be talking. She had been there several months at this point, so they did not look like tourists. And yet, an English couple walked past them and with a whithering gaze said, “Bloody Americans”.

First of all, what’s that all about? It’s a bit difficult to ascribe that merely to orthodonture envy. Secondly, how did they know?!?!?