Post reply

Getting Data from Oracle to Sql Server using a Stored Procedure??

  • February 2, 2011 at 5:03 pm

    #233049

    Hello Folks,

    Am a beginner in Tsql and I have a requirement to write a stored procedure and am all confused how to start and unable to make a headway.

    Usually i have this query which i run every month and get the data into Excel and i just use the Sql Server Import and Export wizard using BIDS and dump the data into SQL server Table. The query is nothing but retreiving the clientcode and employergroup for the previous month from the summary table in Oracle.

    I got the requirement to automate this thing of manually running the Import and Export Wizard for every client every month which takes lot of time.

    So i have the requirement of creating a Stored procedure in sql server to get the data from oracle. Can anyone please help me out on how to start?? The query is below.

    select '801' report_no, -- i have to create this for the reports 801,8,88,802,803

    null CLIENT_ID,

    r.payor_key CLIENT_CODE,

    null CLIENT_NAME,

    r.payor_key legacy_client_code,

    null major_client_name,

    null major_client_code,

    null end_user_id,

    null user_name,

    null employer_group_id,

    replace(r.group_ref_key, '/', ' ') employer_group_code,

    translate(substr(group_name,

    instr(group_name, '-') + 1,

    length(group_name)),

    '/\',

    ' ') employer_group_name,

    '{R08_SUMMARY.PAYOR_KEY} = ' || '"' || r.payor_key || '"' ||

    ' and {R08_SUMMARY.GROUP_NAME} = "' || r.group_name || '"' selform,

    null as path, --

    null email,

    'A' status,

    'EMPGRPWISE CLIENT BO' burst_type,

    'PDF' burst_file_type,

    '-1' id,

    null bouser,

    'G' std_rpt_burst

    from summary r

    where r.month_id = '201012' --i hav to include the logic to get the data every month i have to change this month

    and r.payor_key IN ('HM') -- i have to include the logic to create this for various clients such as HM,BM,CM etc

    and r.print_flag = 'Y'

  • February 8, 2011 at 11:16 am

    #1283895

    This is a duplicate post. Please make any responses here: http://www.sqlservercentral.com/Forums/Topic1058124-338-1.aspx

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply