Can not insert into table that have IDENTITY

Hi All,
I have 2 table. Table one is CREATE TABLE [coba] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
Table two is CREATE TABLE [coba2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
In table coba i insert data such as :
insert into coba (desc) values ('a')
insert into coba (desc) values ('b')
insert into coba (desc) values ('c')
And if i try to show with select * from coba. It will show
1 a
2 b
3 c
The problem is that i want to transfer all of the record in table coba
into coba2. I used this query "insert into coba2 select * from coba".
It show error like this "An explicit value for the identity column in
table 'coba2' can only be specified when a column list is used and
IDENTITY_INSERT is ON."
So, does anyone know how to insert all records in table coba into
coba2?
Thank you very much for your help.I have tried this out and the following will work:
INSERT INTO coba2
SELECT [desc] from coba|||Name the columns for both the INSERT statement as well as the SELECT:
INSERT INTO tbl (col1, col2)
SELECT col1, col2
FROM ...
And consider whether you want to carry over the same idenity values in the target table as you have
in the source table. If so, read about SET IDENTITY_INSERT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"afang" <khokimfang@.gmail.com> wrote in message
news:1146643380.200925.54580@.e56g2000cwe.googlegroups.com...
> Hi All,
> I have 2 table. Table one is CREATE TABLE [coba] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> Table two is CREATE TABLE [coba2] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> In table coba i insert data such as :
> insert into coba (desc) values ('a')
> insert into coba (desc) values ('b')
> insert into coba (desc) values ('c')
> And if i try to show with select * from coba. It will show
> 1 a
> 2 b
> 3 c
> The problem is that i want to transfer all of the record in table coba
> into coba2. I used this query "insert into coba2 select * from coba".
> It show error like this "An explicit value for the identity column in
> table 'coba2' can only be specified when a column list is used and
> IDENTITY_INSERT is ON."
> So, does anyone know how to insert all records in table coba into
> coba2?
> Thank you very much for your help.
>|||Thanks Tibor for your help. It works.
Rgds,
Afangsql