Don Avery (1/3/2011)

---

Actually, I'm right - this query will return 0 rows for both SELECT statements as it will never run - you cannot insert into a table with and Identity PK without specifying the column names on the INSERT query.

INSERT INTO @student
VALUES( 'Hardy', 100);
INSERT INTO @student
VALUES ('Rocky', 98);
INSERT INTO @student
VALUES ('Panky', 99);

Hugo Kornelis (1/3/2011)

---

After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005

john.moreno (1/3/2011)

---

Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.

rejipr1982 (1/3/2011)

---

Thanks.
While working with this type of comma seperated string, first we need to split the string with the help of table valued function.
We can pass the comma deliminated string as an argument to the table valued function.

Also thanks for the insert statement provoded.

I never used this mode of insert statement.

Hugo Kornelis (1/3/2011)

---

Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.

sathishmcc (1/4/2011)

---

Im getting the error while running the query

Server: Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table '@student' can only be specified when a column list is used and IDENTITY_INSERT is ON.