Inserts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserts

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-09 : 14:12:18

I have a query and I get an error for it, can anyone tell me why

declare @somevar int, @ccdid int
set @somevar = 1876

insert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select *
from clientcontractdetail
where clientcontractdetailid = @somevar

set @ccdid = SCOPE_IDENTITY()

insert into clientcontractdetaildisplaytype
select @ccdid, clientcontractdetaildisplaytypeid, clientcontractdetailid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate
from clientcontractdetaildisplaytype
where clientcontractdetailid = @somevar

insert into clientcontractdetailregionsub
select @ccdid, clientcontractdetailid, regionsubid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate
from clientcontractdetailregionsub
where clientcontractdetailid = @somevar

Here is the error

Server: Msg 8101, Level 16, State 1, Line 11
An explicit value for the identity column in table 'clientcontractdetaildisplaytype' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 16
An explicit value for the identity column in table 'clientcontractdetailregionsub' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 14:28:06

You are attempting to insert into the field 'clientcontractdetailid' and it appears that it is an identity column (same as clientcontractdetailregionsub). Remove these from your select part of your insert statement and be sure to define the columns you are inserting into (eg: insert into col1, col2... select col1, col2 from table1 etc...)

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-09 : 14:50:47

Ok I modified it but I am stuck again. I have all sorts of errors about nulls now??

declare @somevar int, @ccdid int
set @somevar = 1876

insert into clientcontractdetail (clientcontractdetailid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select @ccdid, clientcontractid, artworkid, isfixed, placementtypeid, adcount, crosssellingfee, notes, detailstartdate, detailenddate, counttypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate
from clientcontractdetail
where clientcontractdetailid = @somevar

set @ccdid = SCOPE_IDENTITY()

insert into clientcontractdetaildisplaytype (clientcontractdetailid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select @ccdid, displayareatypeid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate
from clientcontractdetaildisplaytype
where clientcontractdetailid = @somevar

insert into clientcontractdetailregionsub (clientcontractdetailid, regionsubid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate)
select @ccdid, regionsubid, initdate, lastupdatedate, lastupdateloginid, lastupdatesql, deletedate
from clientcontractdetailregionsub
where clientcontractdetailid = @somevar

insert into clientcontractdetaildisplaytype (clientcontractdetailid)
values(@ccdid)

insert into clientcontractdetailregionsub (clientcontractdetailid)
values(@ccdid)

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-09 : 15:33:56

Check the design of your tables: looks like clientcontractdetailid is an identity column in table clientcontractdetail and in the table clientcontractdetailregionsub, so you cannot insert into these columns. Take them out of your insert statements or change tables' design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources