Cursor Error

[rute67]

I am getting this error when I have created a cursor. Can someone please help me figure this one out. Probably something small but I just can figure it out.

Server: Msg 213, Level 16, State 5, Line 57
Insert Error: Column name or number of supplied values does not match table definition.


Here is the query:



DECLARE @DC_table table (Member_ID char(15), DC_Enroll_date datetime, DC_Dis_Date datetime,
Rec_Type as char(1), Deter_Code char(1), Deter_Date datetime,
Disenroll_Code char(3), Subsidy_Amt money, Sub_Eff_Date datetime )
DECLARE @Member_ID char(15), @DCE datetime,@DCD datetime,
@RecType as char(1), @DeterCode char(1), @DetDate datetime,
@DS_Code char(3), @SubAmt money, @SubEdate datetime
DECLARE @CurMem char(15),@From datetime, @To datetime,
@RType as char(1), @DeCode char(1), @Det_Date datetime,
@Dis_Code char(3), @Sub_Amt money, @Subdate datetime

DECLARE MyCursor CURSOR
FOR

SELECT
member_ID,
D_C_Enrollment_Date,
D_C_Disenrollment_Date,
Rec_Type_code,
Determination_code,
Determination_Date,
D_C_Disenrollment_Code,
Subsidy_Balance,
Subsidy_Eff_date

FROM D_C_Enrollment_Response
where member_id <> ' '
ORDER BY Member_ID, Determination_Date desc

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @Member_ID, @DCE,@DCD, @RecType, @DeterCode, @DetDate,@DS_Code, @SubAmt, @SubEdate


WHILE @@FETCH_STATUS = 0
BEGIN
set @CurMem = @Member_ID
set @From = @DCE
set @To = @DCD
set @RType = @RecType
set @DeCode = @DeterCode
set @Det_Date = @DetDate
set @Dis_Code = @DS_Code
set @Sub_Amt = @SubAmt
set @SubDate = @SubEDate

FETCH NEXT FROM MyCursor INTO @Member_ID, @DCE,@DCD, @RecType, @DeterCode, @DetDate,@DS_Code, @SubAmt, @SubEdate
WHILE @@FETCH_STATUS = 0 AND @Member_ID = @CurMem

BEGIN
IF @Detdate > @Det_Date
Set @DeterCode = @DeCode

FETCH NEXT FROM MyCursor INTO @Member_ID, @DCE,@DCD, @RecType, @DeterCode, @DetDate,@DS_Code, @SubAmt, @SubEdate

END
INSERT @DC_Table VALUES (@CurMem, @From, @To, @Rtype, @DeCode, @Det_Date, @Dis_Code, @Sub_Amt, @Subdate)
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT
Member_ID,
DC_Enroll_date,
DC_Dis_Date,
Rec_Type,
Deter_Code,
Deter_Date,
Disenroll_Code,
Subsidy_Amt,
Sub_Eff_Date

FROM @DC_Table


ORDER BY Member_ID

 

[mrdenny]

First problem that I see is that you have two fetch next commands in your loop.

The problem statement is going to be your INSERT @DC_Table... statement. Please include your deffination for this table. You have a different number of columns in your table then you are trying to insert. When you do an insert and you do not specify the columns you are inserting, you must insert values for all the columns in the order they are in the table.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[rute67]

Where do you see a different number of columns in my table than the insert. I have 9 columns and I am trying to input 9 items. I have been trying to figure that out for a while now.

 

[mrdenny]

yeah, I'm not sure why it's throwing the error. Try commending out the insert just to be sure.

try it with the columns being inserted in there. Maybe it's because it's a table variable it doesn't like not having the columns listed.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Whay are you doing this with a cursor at all? Inserts should never be done with a cursor.

An insert using a select (with a case statment to take care of the DeterCode problem) should work just fine and be much more efficient.

Speaking of your table variable, I did not see where you defined it. Perhaps the current problem lies in its definition.

Questions about posting. See faq183-874

 

[mrdenny]

I couldn't see it either at first. It's the first statemenet. It just gets blended in with the other declares under it.

I would normally recommend this being done with a single select, but I couldn't come up with select code that would do what he needs to do off the top of my head.

Denny

--Anything is possible. All it takes is a little research. (Me)