Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

T-SQL Expand / Collapse
Author
Message
Posted Monday, January 3, 2011 11:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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.

Actually, you are wrong. The script will run. You don't need to specify column names when inserting into a table with an IDENTITY column.

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 - I replaced the single INSERT with three INSERT statements:
INSERT INTO @student
VALUES( 'Hardy', 100);
INSERT INTO @student
VALUES ('Rocky', 98);
INSERT INTO @student
VALUES ('Panky', 99);

I then hit the F5 button to execute, and I got the expected results.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1041957
Posted Monday, January 3, 2011 11:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question!
Post #1041967
Posted Monday, January 3, 2011 12:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:52 PM
Points: 346, Visits: 1,062
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


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.
Post #1042007
Posted Monday, January 3, 2011 12:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
john.moreno (1/3/2011)
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


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.

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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1042018
Posted Monday, January 3, 2011 12:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 10:11 PM
Points: 84, Visits: 31
You are correct - I'm an idiot!

I guess I never tried inserting without column names being explicitly stated, but it does indeed work.

don



Post #1042020
Posted Monday, January 3, 2011 12:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 10, 2013 9:48 AM
Points: 36, Visits: 74
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.


Thanks.

Reji PR,
Bangalore

Post #1042036
Posted Monday, January 3, 2011 11:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,108, Visits: 1,371
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.
It's good to know that you learn some thing new.


Thanks
Post #1042168
Posted Monday, January 3, 2011 11:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,108, Visits: 1,371
Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
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


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.

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.
Thanks Hugo for covering me. I assume SQL 2008 still its better to mention SQL Server version when we post any query, article or QofD. Next time I will take care


Thanks
Post #1042169
Posted Tuesday, January 4, 2011 12:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 4:16 AM
Points: 330, Visits: 197
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.
Post #1042184
Posted Tuesday, January 4, 2011 12:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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.


Interesting. Did you just copy/paste the code or did you change anything?
What version of SQL Server are you running?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1042192
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse