IDENTITY INSERT

Question

Post reply

IDENTITY INSERT

Lokesh Vij

SSChampion

Points: 10836
More actions
December 12, 2012 at 8:48 pm

#393791

Comments posted to this topic are about the item IDENTITY INSERT
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter

Viewing 15 posts - 1 through 15 (of 43 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Thanks for reminding me of the basics ....

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

demonfox SSCertifiable Points: 6289 More actions · Answer 2

bitbucket-25253 (12/12/2012)
Thanks for reminding me of the basics ....

+1

when I saw identity question , I was sure it's cam out of Lokesh mind's bucket ...

Thanks for the basic reminder..

I did get it wrong though :P:hehe:

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

when I saw identity question , I was sure it's cam out of Lokesh mind's bucket ...

Glad to know that! 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

good question to learn .I got it wrong,

test it after on 2000 box its showing error in insert statement..

I really need to upgrade servers 😉

-----------------------------------------------------------------------------
संकेत कोकणे

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

Nice question. Today i learnt a new one.

--
Dineshbabu
Desire to learn new things..

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

good question Lokesh...

It makes me remind of our last discussion for identity itself... 😛

+1 today also 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 7

kapil_kk (12/13/2012)
good question Lokesh...
It makes me remind of our last discussion for identity itself... 😛
+1 today also 🙂

rightly said. This also made demonfox for fantastically quote like this

when I saw identity question , I was sure it's cam out of Lokesh mind's bucket ...

m glad to know that 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

:-):-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

Nice question, thanks.

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

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 10

Didn't know that limitation so learnt something new today - Thanks!:-D

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

we always learn new things from SqlServer central :-):-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 12

Interesting.

I thought that the second SET IDENTITY_INSERT would automatically set the TblA1 property off. But it actually gives an error.

SET IDENTITY_INSERT TblA1 ON;

GO

SET IDENTITY_INSERT TblA2 ON;

GO

I was kinda confused because setting both options to OFF or any table to OFF for that matter doesn't show any error message as in:

SET IDENTITY_INSERT TblA1 OFF;

GO

SET IDENTITY_INSERT TblA2 OFF;

GO

So thanks for that question as it may be really useful to know when building some complex scripts. I know, we shouldn't mess with identity values anyway. 😀

Best regards,

Andre Guerreiro Neto

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

udayroy15 Ten Centuries Points: 1076 More actions · Answer 13

main thing is:-

IDENTITY_INSERT is already ON for table 'master.dbo.TblA1'. Cannot perform SET operation for table 'TblA2'.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

i chose the wrong option but learned the RIGHT answer:-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)