Inserted Identity

Question

Post reply

Inserted Identity

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Ken SSCoach Points: 17191 More actions · Answer 1

Nice question. Had to step through it a couple of times too.

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 2

easy question!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

SanDroid SSChampion Points: 10068 More actions · Answer 3

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.

SanDroid SSChampion Points: 10068 More actions · Answer 4

Good Friday head scratcher question. Thank you. :smooooth:

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

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;

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 6

Good question, thanks.

http://brittcluff.blogspot.com/

Ken SSCoach Points: 17191 More actions · Answer 7

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

TechnoPeasant SSC-Addicted Points: 447 More actions · Answer 9

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'.

pnewhart SSC Eights! Points: 983 More actions · Answer 10

To really mess with us, I think the author should have included weee,whoa,yay as a possible answer. :laugh:

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 11

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 12

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.

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 13

Yes, the question is good but script could be better.

Regards,

Sudhir

forsqlserver SSCoach Points: 18902 More actions · Answer 14

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

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 15

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