Dear Experts,

I skip all columns name and try a statement "insert into table1 select * 
from table2 where ...."  but always prompt
cannot insert IDENTITY_INSERT is on

Table1 is the same column structure as table2 (actually I created table2 
from running: select * into table2 from table1)

I tried both
SET IDENTITY_INSERT ON , or
SET IDENTITY_INSERT OFF
before insert statement , but also not works.

Could anyone know how to skip typing all columns name when insert from 
another table ?

Thanks. 

thomas (thomas@mail.com) writes:
> I skip all columns name and try a statement "insert into table1 select * 
> from table2 where ...."  but always prompt
> cannot insert IDENTITY_INSERT is on
> 
> Table1 is the same column structure as table2 (actually I created table2 
> from running: select * into table2 from table1)
> 
> I tried both
> SET IDENTITY_INSERT ON , or
> SET IDENTITY_INSERT OFF
> before insert statement , but also not works.
> 
> Could anyone know how to skip typing all columns name when insert from 
> another table ?

The error message says:

   An explicit value for the identity column in table 'X' can only be 
   specified when a column list is used and IDENTITY_INSERT is ON.

That is you need to say:
 
  INSERT table1 (a, b, c, ...)
      SELECT a, b, c, ...
      FROM   table2

Overall, INSERT without column lists in production code is considered
bad practice, as is SELECT *.

One way to skip the actual typing, is to find the table in the Object
Explorer and drag the Columns node into the query window.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx