• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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)
0
Vasi04
Asked:
Vasi04
  • 2
  • 2
1 Solution
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now