Indentity Inserting in SQL Server

When I starting writing this blog, I was a little unsure as to what to put in it. I knew I wanted it to be mostly programming related, with articles about things that interested me. So I named it my ‘Programming Notebook’ and put into words exactly what I envisaged I’d be putting in it. This is what I put in my About page:

My main intention was for this blog was to be a notebook for myself in a presentable format – an aid to memory of things I’ve found useful on a day-to-day basis, and little snippets I’d like to share. A lot of it won’t be ground-breaking and you’ll almost certainly find it around elsewhere on the net, but it’s presented in a way that I learned it in the hope that others might find it useful.

And later on I started fretting that I was duplicating what already ‘out there’, which is nonsense, because almost everything that anyone writes is already ‘out there’ anyway.

None of this has anything to do with Identity Inserting in SQL Server, but maybe goes some way to explaining why I am writing about something that is well documented ‘out there’.

The reason is that I had to look it up twice. I needed to look it once, and then I forgot, so I needed to look it up again. What I needed was some sort of programming noteb…. hey… I have one!

So here it is:

SET IDENTITY_INSERT [MyTable] ON
INSERT INTO [MyTable] (IDField, Field2, Field3, Field4) values (1,212,2,2)
SET IDENTITY_INSERT [MyTable] OFF

You’ll need to set IDENTITY_INSERT ‘ON’ for the table into which you are inserting, and (for good measure) set it back to ‘OFF’ when you’re done.

You’ll also need to include a field list (which is good practice anyway). The following just won’t work:

INSERT INTO [MyTable] values (1,212,2,2)

So there we are, SQL Server Identity Inserting covered….again.

Leave a Reply