Error working with Table Variable 2

[pmcdaniel]

I get the following error when executing the below code:

Error: An explicit value for the identity column in table '@TEMPDC' can only be specified when a column list is used and IDENTITY_INSERT is ON. (State:23000, Native Code: 1FA5)

DECLARE @TEMPDC TABLE
(RowId INT IDENTITY(1,1),
State            varchar(2),
PlanID            varchar(50),
Document          varchar(50))

Insert into @TEMPDC
Select  C.issue_State AS State,
        C.Field169    AS PlanID,
        DC.KeyName    AS Document
From Covg_Data_AIG C
Join Document_Constants DC
ON C.Field169 = DC.PlanId
Where C.issue_State = DC.State
And C.entry_date_time = '2/5/2009 2:58:24 PM'
And C.Rec_id = 'MN20001'
And DC.KeyType = 'MN20001InsertDoc'
And DC.Carrier = 'MN'
Group By C.issue_state, C.Field169, DC.KeyName

Select * From @TEMPDC
GO

This works fine if I take out RowId INT IDENTITY(1,1), but I need it.

I've tried setting SET IDENTITY_INSERT @TEMPDC ON but that doesn't work.


thanks for any help

 

[gmmastros]

Try this...

DECLARE @TEMPDC TABLE
(RowId INT IDENTITY(1,1),
State            varchar(2),
PlanID            varchar(50),
Document          varchar(50))

Insert into @TEMPDC[!](State, PlanId, Document)[/!]
Select  C.issue_State AS State,
        C.Field169    AS PlanID,
        DC.KeyName    AS Document
From Covg_Data_AIG C
Join Document_Constants DC
ON C.Field169 = DC.PlanId
Where C.issue_State = DC.State
And C.entry_date_time = '2/5/2009 2:58:24 PM'
And C.Rec_id = 'MN20001'
And DC.KeyType = 'MN20001InsertDoc'
And DC.Carrier = 'MN'
Group By C.issue_state, C.Field169, DC.KeyName

Select * From @TEMPDC
GO

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Use a column list

Insert into @TEMPDC (State, PlanID, Document)

 

[pmcdaniel]

YES YES YES!!!

Thanks much to both of you.