Insert from cursor
-
I need to insert cursor data into a table, but I can't seem to get the sql pounded out right. Can someone please help.
-
For us to help you, you need to show us what you have already done, and what you are trying to accomplish.
-
Here is what I have done so far.
/*
This code creates a cursor for the specific data_ob where rec_type = 'F'.
It also create a nested cursor of the fiscal_month to check for missing months
in STG_FRSOM_DAT7 where the OB budget information needs to roll forward.
The concept is that if there is activity for a given month, then the code goes on to
the next record. If there is a month missing, then the code inserts the last month number
it checked plus one increment (i.e. missing month 8 - last month checked was 7 - month
inserted is (7+1)... being 8) and rolls the balance forward.
*/
DECLARE @data_mochar(2)
DECLARE data_cursor cursor for
SELECT FRSOM_ACCT_KEY
,FRSOM_ORG_KEY
,FRSOM_PROF_KEY
,FRSOM_FISCAL_KEY
,DEPTFUND
,DATASSN
,LC
,LEDGER
,CLASS
,DEPT
,FUND1
,FUND
,ZFUND
,INV1
,HYCLACCT
,CARB_USED
,STEPDOWN
,METHOD
,HYPSQ
,WUX_FLAG
,BUOB
,DATATYPE
,REC_TYPE
,FY
,ACB
,AOB
,APCB
,APOB
,DATA_CB
,DATA_OB
,PREV_CB
,PREV_OB
,FYACCT
,ZFYACCT
,TEST_FY
,Y2K_FY
,YY_FY
,MONTH
,AMA
,APMA
,PREV_MA
FROM TEST_FINANCIALDW.DBO.STG_FRSOM_DAT7
WHERE (REC_TYPE = 'A'
OR REC_TYPE = 'C'
OR REC_TYPE = 'F'
OR REC_TYPE = 'V')
open data_cursor
fetch next from data_cursor
while @@fetch_status -1
BEGIN
DECLARE month_cursor cursor for
select DISTINCT(FISCAL_MONTH) from TEST_FINANCIALDW.DBO.DIM_FISCAL_INFO order by FISCAL_MONTH
open month_cursor
fetch next from month_cursor
while @@fetch_status -1
Begin
DECLARE @month char(2)
set @data_mo = ''
set @month = @data_mo
-- set @month = ''
-- set @data_mo = @month
if @month = @data_mo
if @@fetch_status = 0
fetch next from month_cursor
if @month @data_mo
INSERT INTO TEST_FINANCIALDW.DBO.STG_FRSOM_DAT7
(FRSOM_ACCT_KEY
,FRSOM_ORG_KEY
,FRSOM_PROF_KEY
,FRSOM_FISCAL_KEY
,DEPTFUND
,DATASSN
,LC
,LEDGER
,CLASS
,DEPT
,FUND1
,FUND
,ZFUND
,INV1
,HYCLACCT
,CARB_USED
,STEPDOWN
,METHOD
,HYPSQ
,WUX_FLAG
,BUOB
,DATATYPE
,REC_TYPE
,FY
,ACB
,AOB
,APCB
,APOB
,DATA_CB
,DATA_OB
,PREV_CB
,PREV_OB
,FYACCT
,ZFYACCT
,TEST_FY
,Y2K_FY
,YY_FY
,MONTH
,AMA
,APMA
,PREV_MA)
SELECT FRSOM_ACCT_KEY
,FRSOM_ORG_KEY
,FRSOM_PROF_KEY
,FRSOM_FISCAL_KEY
,DEPTFUND
,DATASSN
,LC
,LEDGER
,CLASS
,DEPT
,FUND1
,FUND
,ZFUND
,INV1
,HYCLACCT
,CARB_USED
,STEPDOWN
,METHOD
,HYPSQ
,WUX_FLAG
,BUOB
,DATATYPE
,REC_TYPE
,FY
,ACB
,AOB
,APCB
,APOB
,DATA_CB
,DATA_OB
,PREV_CB
,PREV_OB
,FYACCT
,ZFYACCT
,TEST_FY
,Y2K_FY
,YY_FY
,(@data_mo +1)
,AMA
,APMA
,PREV_MA
from data_cursor
if @@fetch_status = 0
fetch next from data_cursor
end
close month_cursor
deallocate month_cursor
end
close data_cursor
deallocate data_cursor
-
I don't see you putting the record into a variable when you do fetch next.
i.e. Fetch Next from cursorname into @Variable
Your cursor itself will only have the distinct months in it, not that entire list of columns you are trying to insert, you would most likely have to use that @Variable in a where clause in the insert.
-
This can be done with 3 or 4 SET operations.
With this, you get all year at once!
SELECT
d.FRSOM_ACCT_KEY,
d.FRSOM_ORG_KEY,
d.FRSOM_PROF_KEY,
d.FRSOM_FISCAL_KEY,
d.DEPTFUND,
d.DATASSN,
d.LC,
d.LEDGER,
d.CLASS,
d.DEPT,
d.FUND1,
d.FUND,
d.ZFUND,
d.INV1,
d.HYCLACCT,
d.CARB_USED,
d.STEPDOWN,
d.METHOD,
d.HYPSQ,
d.WUX_FLAG,
d.BUOB,
d.DATATYPE,
d.REC_TYPE,
d.FY,
d.ACB,
d.AOB,
d.APCB,
d.APOB,
d.DATA_CB,
d.DATA_OB,
d.PREV_CB,
d.PREV_OB,
d.FYACCT,
d.ZFYACCT,
d.TEST_FY,
d.Y2K_FY,
d.YY_FY,
d.MONTH,
d.AMA,
d.APMA,
d.PREV_MA
FROM (
SELECT DISTINCT FISCAL_MONTH
FROM TEST_FINANCIALDW.DBO.DIM_FISCAL_INFO
) AS x
LEFT JOIN TEST_FINANCIALDW.DBO.STG_FRSOM_DAT7 AS d ON d.MONTH = x.FISCAL_MONTH AND d.REC_TYPE IN ('A', 'C', 'F', 'V')
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply