If you run into the following error message:
An explicit value for the identity column in table ‘<TABLE_NAME>’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.
One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:
SET IDENTITY_INSERT table_name ON
And then turn it off again when done
SET IDENTITY_INSERT table_name OFF
However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:
INSERT INTO target_able_name (column_name1, column_name2…. column_nameN)
SELECT
YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name
I wasn’t aware of the latter syntax myself before I got the error message today. But then I learned a little something new today after all 
could not help this for following code at all
Insert into Database1.[Dbo].[Tablename]select * from Database2.[Dbo].[Tablename]
It worked thanks
It work for me too, thanks.
[b]Thanks![/b]
works like a charm =]
could not help this for following code at all
[...] example a page like: An explicit value for the identity column in table can only be specified when a column list is used … (yes, long title, too long I know – but it matches an error in SQL Server directly and [...]
Still works great, even on 2008. Thanks!
thanks got the solution
So you’re telling me there’s NO WAY in M.S. SQL to INSERT INTO the same table (generating new IDs) using a SELECT without specifying EVERY column name but the ID?
I guess hindsight is 20/20 huh? MySQL has this bug FIXED.
It’s needed when inserting identity values (basically overruling the identity function) yourself, not when having SQL Server auto-generating the identity.
Maybe someone would have the same problem.
I have changed one column in my table to have computed value.
After that my stored procedure for insert started throwing this error.
Just comment/delete parameter from insert query and everything would be fine
An explicit value for the identity column in ‘table’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
I’ve changed key field and place PK as key field.It doesn’t work, again reveals same error. please suggest. An explicit value for the identity column in ‘table’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
Dont specify the primary key, it is auto generated.
Thanks