Missing key

Question

Missing key

pallavi.unde

SSCommitted

Points: 1987
More actions
March 15, 2012 at 5:45 am

#254765

The ProductCategory table has the following table definition:CREATE TABLE [dbo].[ProductCategory] ( [ProductCategoryID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1), [ProductCategoryName] NVARCHAR(100) ) The first few rows in the table are as follows:ProductCategoryID ProductCategoryName ------------------- --------------------- 1 Soap 2 Shampoo 4 Toothpaste 5 Toothbrush To fill out the unused identity value, the product category "Conditioner" will be assigned a value of 3 for the product category ID. Which of the following Transact-SQL statement will allow you to INSERT this record in this table?
A. SET IDENTITY_INSERT [dbo].[ProductCategory] ON
INSERT INTO [dbo].[ProductCategory]
VALUES ( 3, 'Conditioner')
B.SET IDENTITY_INSERT [dbo].[ProductCategory] ON
INSERT INTO [dbo].[ProductCategory]
( [ProductCategoryID], [ProductCategoryName] )
VALUES ( 3, 'Conditioner' )
c.SET IDENTITY_INSERT [dbo].[ProductCategory] ON
INSERT INTO [dbo].[ProductCategory]
( [ProductCategoryName] )
VALUES ( 'Conditioner' )
D.INSERT INTO [dbo].[ProductCategory]
( [ProductCategoryID], [ProductCategoryName] )
VALUES ( 3, 'Conditioner' )
E. None of the Above
Thanks & Regards,
Pallavi

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It's not a Poll! It's looks like an interview questions and there is nothing to discuss about! I guess you need to do some sort of research/learning... Google usually helps!

Or, even simpler: just try all of then in SSMS and you will see what does work and what doesn't.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Homework?

What do you think it is and why? hit: read up on identity columns and identity insert in SQL Books Online

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

It seems there are 2 correct answers :w00t:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Koen Verbeeck (3/15/2012)
It seems there are 2 correct answers :w00t:

No, there's only one that will both work and do what the question requires.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Koen Verbeeck (3/15/2012)
It seems there are 2 correct answers :w00t:

Finally! I knew quantum mechanics would sooner or later apply to our trade 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

GilaMonster (3/15/2012)
Koen Verbeeck (3/15/2012)
It seems there are 2 correct answers :w00t:
No, there's only one that will both work and do what the question requires.

Ah, I see.

I didn't know about that error. Mostly because I usually don't insert identity values to close a gap.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CapnHector SSCoach Points: 16143 More actions · Answer 7

PaulB-TheOneAndOnly (3/15/2012)
Koen Verbeeck (3/15/2012)
It seems there are 2 correct answers :w00t:
Finally! I knew quantum mechanics would sooner or later apply to our trade 😀

but alas the entanglement does not last past a first glance.

i think its G for google it Here is the link for the google search.

http://lmgtfy.com/?q=identity+insert

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]