question

varaprasad avatar image
varaprasad asked

Identity_Insert

Hi, I have to Explicit value to Identity column, So I Issued the statements, SET IDENTITY_INSERT Client.Client ON; INSERT INTO Client.Client SELECT 1, ClientTypeID, Name, FirstName, MiddleName, LastName, DateOfBirth, IsSendBirthDayWishes, FathersName, GenderID FROM dbo.Client But,I got the error, An explicit value for the identity column in table 'Client.Client' can only be specified when a column list is used and IDENTITY_INSERT is ON. What Does it Mean? If I remove the expicit value 1, SET IDENTITY_INSERT Client.Client ON; INSERT INTO Client.Client SELECT ClientTypeID, Name, FirstName, MiddleName, LastName, DateOfBirth, IsSendBirthDayWishes, FathersName, GenderID FROM dbo.Client An error, Explicit value must be specified for identity column in table 'Client' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. can any one tell me the mistake in this
sql-server-2008t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
First of all, I would specify the columns that you should insert the values into: SET IDENTITY_INSERT Client.Client ON; INSERT INTO Client.Client ( ClientID, --assuming this is your identity column ClientTypeID, Name, FirstName, MiddleName, LastName, DateOfBirth, IsSendBirthDayWishes, FathersName, GenderID ) SELECT 1, ClientTypeID, Name, FirstName, MiddleName, LastName, DateOfBirth, IsSendBirthDayWishes, FathersName, GenderID FROM dbo.Client Next, how many records are you trying to insert from the dbo.client table? I guess your exception can occur from the fact that you are trying to insert the same value for every record in the identity column. Maybe you should try to use ROW_NUMBER() to create incrementing numbers, or turn IDENTITY_INSERT off to let SQL server generate your identity values.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
When you have a table with a column that uses the IDENTITY setting SQL Server will assign values for that column to any rows that are inserted. CREATE TABLE Test (PersonID INT IDENTITY, pName varchar(50)) This means that when you insert rows to the table you dont have to include that column in your statement. INSERT INTO Test (pName) SELECT 'Fatherjack' This also means that if you want to control the values that are being put in there (perhaps you have to import a set of data from somewhere and preserve the values in that set) then you have to tell SQL Server to not try and assign the automatic values SET IDENTITY_INSERT ON -- this allows you to specify a value for PersonID INSERT INTO Test (PersonID, pNname) VALUES (99, 'Dougal') SET IDENTITY_INSERT OFF -- This lets SQL Server take control of PersonID values again
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.