December 11, 2009 at 8:59 am
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.Application_param' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I am getting this error when i am trying insert data
i tried using set identity_insert off/on
can anyone please help me on this
December 11, 2009 at 9:13 am
Have you also specified the column list
Example
CREATE TABLE [dbo].[TestTable](
[Identitycol] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
You need to use
SET IDENTITY_INSERT TESTTABLE ON
INSERT INTO dbo.TestTable ([Identitycol])
VALUES
(234)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 11, 2009 at 9:37 am
I did try all this .
is there anyway before i can remove the identity column as non - identity
after insert i can change it to identity column .
December 11, 2009 at 10:09 am
Post here what have you tried, with table DDL and what's your insert script, it should work and it is better to do it this way.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 11, 2009 at 10:09 am
dba.nivali (12/11/2009)
I did try all this .is there anyway before i can remove the identity column as non - identity
after insert i can change it to identity column .
No there isn't, not without creating a new table, and using identity insert to insert the data into it from the table that doesn't have the identity column.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
December 11, 2009 at 11:19 am
Yes i got it , i did an insert with the table column in it , without the identity column and it worked .
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply