Cursor approach to insert rows

Can some one pls help me to have this code in CURSOR method. Thanks so much
-----------------------

insert into table1
(_INTERNID, _CHGDATE, _CHGTIME, _STAMP2, _ID, _ID2, _CREATE_DATE, _CREATE_BY, _CHANGED_BY, _CHANGED_DATE,
_YEAR, _YEAR_S, _STATUS1, _BUS_SPON, _IMPORTED, _DOCS_FIM, _DOCS_RTN, _SEGMENT, _TP, _SC_POTEN, _PRIO1, _PRIO1A, _PRIO2,
_PRIO2A, _PRIO3, _PRIO3A, _PO_NNM1, _PO_NNM2, _PO_NNM3, _SOW_BANK1, _SOW_BANK2, _SOW_BANK3, _SOW_BANK4, _SOW_BANK5,
_SOW_BANK6, _SOW_MONEY1, _SOW_MONEY2, _SOW_MONEY3, _SOW_MONEY4, _SOW_MONEY5, _SOW_MONEY6, _SOW_DATE1, _SOW_DATE2,
_SOW_DATE3, _SOW_DATE4, _SOW_DATE5, _SOW_DATE6, _BUS_SPON_RTN, _ST1_RTN, _ST2_RTN, _BUS_RTN,
_KEY_RTN, _IN_NET, _SQL, _EFFECTIVE, _PROBABILITY)


select _INTERNID, _CHGDATE, _CHGTIME, _STAMP2 + 10000000, @result, _ID, getdate(), _CREATE_BY,_CHANGED_BY, getdate(),
_YEAR, YEAR (getdate()), 'Generated',
_BUS_SPON, _IMPORTED, _DOCS_FIM, _DOCS_RTN, _SEGMENT, _TP, _SC_POTEN, _PRIO1, _PRIO1A, _PRIO2, _PRIO2A, _PRIO3, _PRIO3A, _PO_NNM1,_PO_NNM2, _PO_NNM3,
_SOW_BANK1, _SOW_BANK2, _SOW_BANK3, _SOW_BANK4, _SOW_BANK5, _SOW_BANK6, _SOW_MONEY1, _SOW_MONEY2, _SOW_MONEY3, _SOW_MONEY4, _SOW_MONEY5, _SOW_MONEY6,
_SOW_DATE1, _SOW_DATE2, _SOW_DATE3, _SOW_DATE4, _SOW_DATE5, _SOW_DATE6, _BUS_SPON_RTN, _ST1_RTN, _ST2_RTN, _BUS_RTN,
_KEY_RTN, _IN_NET, _SQL, _EFFECTIVE, _PROBABILITY

from table1 where _YEAR = YEAR(getdate()-1)

UPDATE table1
SET _YEAR=_YEAR+1
WHERE _CREATE_DATE = ( SELECT MAX(_CREATE_DATE) FROM table1)

UPDATE table1
SET _STATUS1 = 'Freezed',
_changed_date = getdate()-1
WHERE _CREATE_DATE < ( SELECT MAX(_CREATE_DATE) FROM table1)
Vasi04Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KarinLoosCommented:
is this qeustion related to your other question ?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21209716.html
I posted a cursor example there
0
Vasi04Author Commented:
yes KArin, thanks a lot
0
Vasi04Author Commented:
BEGIN
DECLARE query CURSOR FOR
SELECT
_INTERNID, _CHGDATE, _CHGTIME, _STAMP2 + 10000000, @result, _ID, getdate(), _CREATE_BY, _CHANGED_BY, getdate(),
_YEAR, YEAR (getdate()), 'Generated',
_BUS_SPON, _IMPORTED, _DOCS_FIM, _DOCS_RTN, _SEGMENT, _TP, _SC_POTEN, _PRIO1, _PRIO1A, _PRIO2, _PRIO2A, _PRIO3, _PRIO3A,
_PO_NNM1,_PO_NNM2, _PO_NNM3, _SOW_BANK1, _SOW_BANK2, _SOW_BANK3, _SOW_BANK4, _SOW_BANK5, _SOW_BANK6, _SOW_MONEY1,
_SOW_MONEY2, _SOW_MONEY3, _SOW_MONEY4, _SOW_MONEY5, _SOW_MONEY6,_SOW_DATE1, _SOW_DATE2, _SOW_DATE3, _SOW_DATE4,
_SOW_DATE5, _SOW_DATE6, _BUS_SPON_RTN, _ST1_RTN, _ST2_RTN, _BUS_RTN,_KEY_RTN, _IN_NET, _SQL, _EFFECTIVE, _PROBABILITY
from RMP where _YEAR = YEAR(getdate()-1)

set @sqlstr = 'INSERT INTO RMP (_INTERNID, _CHGDATE, _CHGTIME, _STAMP2, _ID, _ID2, _CREATE_DATE, _CREATE_BY, _CHANGED_BY, _CHANGED_DATE,
_YEAR, _YEAR_S, _STATUS1, _BUS_SPON, _IMPORTED, _DOCS_FIM, _DOCS_RTN, _SEGMENT, _TP, _SC_POTEN, _PRIO1, _PRIO1A, _PRIO2,
_PRIO2A, _PRIO3, _PRIO3A, _PO_NNM1, _PO_NNM2, _PO_NNM3, _SOW_BANK1, _SOW_BANK2, _SOW_BANK3, _SOW_BANK4, _SOW_BANK5,
_SOW_BANK6, _SOW_MONEY1, _SOW_MONEY2, _SOW_MONEY3, _SOW_MONEY4, _SOW_MONEY5, _SOW_MONEY6, _SOW_DATE1, _SOW_DATE2,
_SOW_DATE3, _SOW_DATE4, _SOW_DATE5, _SOW_DATE6, _BUS_SPON_RTN, _ST1_RTN, _ST2_RTN, _BUS_RTN,
_KEY_RTN, _IN_NET, _SQL, _EFFECTIVE, _PROBABILITY) values ('

set @counter = 0

OPEN query
FETCH NEXT FROM query INTO @INTERNID, @CHGDATE, @CHGTIME, @STAMP2, @ID, @ID2, @CREATE_DATE, @CREATE_BY, @CHANGED_BY, @CHANGED_DATE,
@YEAR, @YEAR_S, @STATUS1, @BUS_SPON, @IMPORTED, @DOCS_FIM, @DOCS_RTN, @SEGMENT, @TP, @SC_POTEN, @PRIO1, @PRIO1A, @PRIO2,
@PRIO2A, @PRIO3, @PRIO3A, @PO_NNM1, @PO_NNM2, @PO_NNM3, @SOW_BANK1, @SOW_BANK2, @SOW_BANK3, @SOW_BANK4, @SOW_BANK5,
@SOW_BANK6, @SOW_MONEY1, @SOW_MONEY2, @SOW_MONEY3, @SOW_MONEY4, @SOW_MONEY5, @SOW_MONEY6, @SOW_DATE1, @SOW_DATE2,
@SOW_DATE3, @SOW_DATE4, @SOW_DATE5, @SOW_DATE6, @BUS_SPON_RTN, @ST1_RTN, @ST2_RTN, @BUS_RTN,
@KEY_RTN, @IN_NET, @SQL, @EFFECTIVE, @PROBABILITY

WHILE (@@FETCH_STATUS = 0)
BEGIN
exec shortstamp @date, @hsecs, @result OUTPUT
set @counter = @counter + 1
set @sqlstr =  @sqlstr + @space  + '''' +  @INTERNID + ''', ''' + @CHGDATE + ''', ''' + @CHGTIME + ''', '''
 + @STAMP2  + ''', ''' + @result + ''', ''' + @ID  + ''', ''' + @CREATE_DATE + ''', ''' + @CREATE_BY + ''', '''
 + @CHANGED_BY + ''', ''' + @CHANGED_DATE + ''', ''' + @YEAR + ''', ''' + @YEAR_S + ''', ''' + @STATUS1 + ''', '''
 + @BUS_SPON + ''', ''' + @IMPORTED + ''', ''' + @DOCS_FIM + ''', ''' + @DOCS_RTN + ''', ''' + @SEGMENT + ''', '''
 + @TP + ''', ''' + @SC_POTEN  + ''', ''' + @PRIO1  + ''', ''' + @PRIO1A + ''', ''' + @PRIO2 + ''', '''
 + @PRIO2A + ''', ''' + @PRIO3 + ''', ''' + @PRIO3A + ''', ''' + @PO_NNM1 + ''', ''' + @PO_NNM2 + ''', '''
 + @PO_NNM3 + ''', ''' + @SOW_BANK1 + ''', ''' + @SOW_BANK2 + ''', ''' + @SOW_BANK3 + ''', '''
 + @SOW_BANK4 + ''', ''' + @SOW_BANK5 + ''', ''' + @SOW_BANK6 + ''', ''' + @SOW_MONEY1 + ''', '''
 + @SOW_MONEY2 + ''', ''' + @SOW_MONEY3 + ''', ''' + @SOW_MONEY4 + ''', ''' + @SOW_MONEY5 + ''', '''
 + @SOW_MONEY6 + ''', ''' + @SOW_DATE1 + ''', ''' + @SOW_DATE2 + ''', ''' + @SOW_DATE3 + ''', '''
 + @SOW_DATE4 + ''', ''' + @SOW_DATE5 + ''', ''' + @SOW_DATE6 + ''', ''' + @BUS_SPON_RTN + ''', '''
 + @ST1_RTN + ''', ''' + @ST2_RTN + ''', ''' + @BUS_RTN + ''', ''' + @KEY_RTN + ''', ''' + @IN_NET + ''', '''
 + @SQL + ''', ''' + @EFFECTIVE + ''', ''' + @PROBABILITY + ''''

set @cursor_fetch_state = @@fetch_status
set @space = ','

FETCH NEXT FROM query INTO @INTERNID, @CHGDATE, @CHGTIME, @STAMP2, @ID, @ID2, @CREATE_DATE, @CREATE_BY, @CHANGED_BY, @CHANGED_DATE,
@YEAR, @YEAR_S, @STATUS1, @BUS_SPON, @IMPORTED, @DOCS_FIM, @DOCS_RTN, @SEGMENT, @TP, @SC_POTEN, @PRIO1, @PRIO1A, @PRIO2,
@PRIO2A, @PRIO3, @PRIO3A, @PO_NNM1, @PO_NNM2, @PO_NNM3, @SOW_BANK1, @SOW_BANK2, @SOW_BANK3, @SOW_BANK4, @SOW_BANK5,
@SOW_BANK6, @SOW_MONEY1, @SOW_MONEY2, @SOW_MONEY3, @SOW_MONEY4, @SOW_MONEY5, @SOW_MONEY6, @SOW_DATE1, @SOW_DATE2,
@SOW_DATE3, @SOW_DATE4, @SOW_DATE5, @SOW_DATE6, @BUS_SPON_RTN, @ST1_RTN, @ST2_RTN, @BUS_RTN,
@KEY_RTN, @IN_NET, @SQL, @EFFECTIVE, @PROBABILITY

END
CLOSE query
DEALLOCATE query
set @sqlstr = @sqlstr + ')'
exec(@sqlstr)
END

------------------------------------------------------------------------------------------------
I am getting this error in QA:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

Any idea? I am stuck. Pls help
0
KarinLoosCommented:
hmmm,  
well firstly why are you building up an insert string in the cursor?
secondly what is the point of the statements:
set @cursor_fetch_state = @@fetch_status
set @space = ','
thirdly in your select statement at the top, you dont need to select getDate(),  as you can using the Getdate() function
in the actual insert statement.
and lastly  just a point of interest the where condition : YEAR(getdate()-1 ) will only return the previous year on 1st january of every year lol, if you want last year then YEar(Getdate()) - 1 would be better.
example:
DECLARE queryCursor CURSOR FOR
select ....(list of your fields excluding the getdate() ones ..  
from RMP
where _YEAR = YEAR(getdate()-1)
OPEN queryCursor
FETCH NEXT FROM queryCursor INTO @INTERNID .....
while @@FETCH_STATUS = 0
  begin
     exec shortstamp @date, @hsecs, @result OUTPUT
     INSERT INTO RMP (_INTERNID..   )
     VALUES ( @inertnedID... list of all the variables from the cursor  & your @result one you need.. where you need the getdate(), you      
                     can  now put getdate()  )
   
     FETCH NEXT FROM queryCursor INTO @INTERNID......
  end
CLOSE queryCursor
DEALLOCATE queryCursor

thats it...
HTH


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.