August 26, 2011 at 8:52 am
easy question!!
August 26, 2011 at 8:53 am
Thomas Abraham (8/26/2011)
SQLkiwi (8/25/2011)
A good question, though the example was more complex than it needed to be.Agreed. Although it did simulate a lot of maintenance programming I've had to do. Reminded me of situations where you'd wonder just what the heck is this guy trying to do here?
Thanks for the good Friday morning laugh.
So many diverse people and personalities with the same thoughts and ideas about the QOTD.
August 26, 2011 at 8:55 am
Good Friday head scratcher question. Thank you. :smooooth:
August 26, 2011 at 9:14 am
Kenneth Wymore (8/26/2011)
Nice question. Had to step through it a couple of times too.
Yes, this would have saved me a little time, but it is Friday.
CREATE TABLE #T1 (T1ID INT);
CREATE TABLE #T2 (
T2ID INT IDENTITY(1,1),
T1ID INT);
DECLARE @temp TABLE (ID INT)
INSERT INTO #T1 (T1ID) VALUES (4);
INSERT INTO #T1 (T1ID) VALUES (5);
INSERT INTO #T1 (T1ID) VALUES (6);
INSERT INTO #T2
OUTPUT inserted.T2ID INTO @temp
SELECT T1ID FROM #t1;
SELECT * FROM @temp;
DROP TABLE #T1;
DROP TABLE #T2;
August 26, 2011 at 9:25 am
Good question, thanks.
http://brittcluff.blogspot.com/
August 26, 2011 at 9:30 am
Cliff Jones (8/26/2011)
Kenneth Wymore (8/26/2011)
Nice question. Had to step through it a couple of times too.Yes, this would have saved me a little time, but it is Friday.
CREATE TABLE #T1 (T1ID INT);
CREATE TABLE #T2 (
T2ID INT IDENTITY(1,1),
T1ID INT);
DECLARE @temp TABLE (ID INT)
INSERT INTO #T1 (T1ID) VALUES (4);
INSERT INTO #T1 (T1ID) VALUES (5);
INSERT INTO #T1 (T1ID) VALUES (6);
INSERT INTO #T2
OUTPUT inserted.T2ID INTO @temp
SELECT T1ID FROM #t1;
SELECT * FROM @temp;
DROP TABLE #T1;
DROP TABLE #T2;
Cliff,
I like your example better. More straightforward and easier to read.
August 26, 2011 at 10:13 am
Good question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 10:16 am
The target environment was undefined so "an error" is a valid answer. ?
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'MAX'.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'MAX'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'OUTPUT'.
August 26, 2011 at 2:06 pm
To really mess with us, I think the author should have included weee,whoa,yay as a possible answer. :laugh:
August 26, 2011 at 2:44 pm
Sometimes when something is very obvious I think to myself that there must be a catch or an SQL Server limitation of some sort. The only other possible answer was "an error" but the code was right to my eyes.
Damn you for making me read and re-read the question 4 times. :w00t:
And thanks for the question.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
August 26, 2011 at 7:12 pm
Very good question indeed!
Took me a while to understand the "OUTPUT inserted.id" until I modified the query and finaly realised that the ".id" from the "OUTPUT inserted.id" was comming from t2 table :w00t:
Learning again
Thanks for the good question.
August 28, 2011 at 12:08 am
Yes, the question is good but script could be better.
Regards,
Sudhir
August 28, 2011 at 11:25 pm
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
Thanks
August 29, 2011 at 10:18 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
That is just one statement. Here's how it breaks down:
INSERT INTO t2 --The insert is the actual statement
OUTPUT inserted.id INTO @temp --output is a clause that returns the information specified
SELECT d.id, t1.value FROM t1 --And the rest is what's being inserted into t2.
INNER JOIN define d ON t1.name = d.name
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
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy