set identity_insert not works for insert statement on SQL2..

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 RemoveThis @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