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

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?
sp_spaceused has two result sets, so no you can't do that directly...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>
|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> table
>
|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?sp_spaceused has two result sets, so no you can't do that directly...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> table
>|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?sp_spaceused has two result sets, so no you can't do that directly...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>|||You can do that at a table level:
Example for Northwind Employees table.
Create Table #Temp1
(Tablename CHAR(80),
rows int,
un_space VARCHAR(50),
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50))
GO
Insert into #Temp1
Exec sp_spaceused 'Employees'
GO
Select * from #Temp1
GO
Drop table #Temp1
GO
>--Original Message--
>Firstly, I created a table : test01 as follow :
>Field Data type
>db_name char (80)
>db_size char (20)
>unallocated_space char (20)
>reserved char (20)
>data char (20)
>index_size char (20)
>unused char (20)
>Then, I executed this command in SQL Analyzer :
>INSERT INTO test01 EXEC sp_spaceused
>But I only got this result :
>Server: Msg 213, Level 16, State 7, Procedure
sp_spaceused, Line 142
>Insert Error: Column name or number of supplied values
does not match table
>definition
>
>Can I output the result of : EXEC sp_spaceused to a
table ?
>Was my SQL statement correct or incorrect ?
>
>.
>|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> > Firstly, I created a table : test01 as follow :
> >
> > Field Data type
> > db_name char (80)
> > db_size char (20)
> > unallocated_space char (20)
> > reserved char (20)
> > data char (20)
> > index_size char (20)
> > unused char (20)
> >
> > Then, I executed this command in SQL Analyzer :
> >
> > INSERT INTO test01 EXEC sp_spaceused
> >
> > But I only got this result :
> >
> > Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> > Insert Error: Column name or number of supplied values does not match
> table
> > definition
> >
> >
> > Can I output the result of : EXEC sp_spaceused to a table ?
> > Was my SQL statement correct or incorrect ?
> >
> >
> >
>|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A|||Thanks to everybodies.
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:115ac01c441a1$917301b0$a001280a@.phx.gbl...
> You can do that at a table level:
> Example for Northwind Employees table.
>
> Create Table #Temp1
> (Tablename CHAR(80),
> rows int,
> un_space VARCHAR(50),
> reserved VARCHAR(50),
> data VARCHAR(50),
> index_size VARCHAR(50))
> GO
> Insert into #Temp1
> Exec sp_spaceused 'Employees'
> GO
> Select * from #Temp1
> GO
> Drop table #Temp1
> GO
>
> >--Original Message--
> >Firstly, I created a table : test01 as follow :
> >
> >Field Data type
> >db_name char (80)
> >db_size char (20)
> >unallocated_space char (20)
> >reserved char (20)
> >data char (20)
> >index_size char (20)
> >unused char (20)
> >
> >Then, I executed this command in SQL Analyzer :
> >
> >INSERT INTO test01 EXEC sp_spaceused
> >
> >But I only got this result :
> >
> >Server: Msg 213, Level 16, State 7, Procedure
> sp_spaceused, Line 142
> >Insert Error: Column name or number of supplied values
> does not match table
> >definition
> >
> >
> >Can I output the result of : EXEC sp_spaceused to a
> table ?
> >Was my SQL statement correct or incorrect ?
> >
> >
> >
> >.
> >