August 29, 2011 at 11:24 am
forsqlserver (8/28/2011)
Hi,Good question.
Does below written statements is one query statment:
INSERT INTO t2
OUTPUT inserted.id INTO @temp
SELECT d.id, t1.value FROM t1
INNER JOIN define d ON t1.name = d.name
All one statement. When people use layout to make their code easier to read, it's easy to see where statement boundaries are; when they don't you have to parse the SQL to find the boundaries, which is a bit boring (and, when someone is taking the p, not terribly easy, as demonstrated intentionally bey many QotD and accidentally by more).
Hereare two ways it could be written to make it clear
INSERT INTO t2
OUTPUT inserted.id INTO @temp
SELECT d.id, t1.value
FROM t1 INNER JOIN define d
ON t1.name = d.name
INSERT INTO t2 OUTPUT inserted.id INTO @temp
SELECT d.id, t1.value FROM t1 INNER JOIN define d ON t1.name = d.name
<rant>Developers in other languages generally get using layout for readability dinned into them very early in their career (or in their pre-career education); developers in SQL (and DBAs) often don't, which is a pity. An even worse pity is that people quarrel about trivia in connection with layout for readability - for example should a tab be 2,3,4,6 or 8 spaces, or have standards forced down their throats (often by managers who neither read nor write any code, or who think SQL should have the same layout rules as some unrelated language with completely different syntactic structure that they learned 15 years ago and have never used since, of think the "flying ducks effect" is important and should be visible even in code that makes no use of block-structured control flow) that prevent them using good auto-layout tools because the standard requires bad layout.</rant>
Tom
August 29, 2011 at 12:47 pm
Tom.Thomson (8/26/2011)
I suppose some might go for the "error" answer if they think that "define" is a reserved word that would need to be quoted when used as a table name, but I can't think of any T-SQL syntax that uses "define" in a way that would require it to be a reserved word so I don't know why anyone would think that. But 22% so have have thought that (or derampt up some other imaginary error).If it weren't for the fact that 22% of answers so far are "error" I would be predicting a high 90s correct answer rate for this one.
It's a syntactical thing for me. OUTPUT I knew, I expected it to whine about the lack of the column list on t2 since only two inputs were inbound from t1.
Good question though. I learned something, even if it had nothing to do with the intended question.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 30, 2011 at 8:12 am
Is it only me that is struggling to understand how we get 3 columns from a 1 column table..?
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply