0

I have a requirement to create a new database on SQL Server from an existing Peoplesoft database, which is on oracle. I already have the script with me, and I want to know if the data can be extracted from the Peoplesoft database to the newly created SQL Server database using linked servers?

This is the script:

Create Database LHR_MIGRATION
go
Use LHR_MIGRATION
go
Create Table dbo.Z_MIGRATION_TABLE 
(
 AMOUNT_TYPE nvarchar(255) NULL
,BUSINESS_UNIT nvarchar(255) NULL
,LEDGER nvarchar(255) NULL
,ACCOUNT nvarchar(255) NULL
,DESCR nvarchar(255) NULL
,DEPTID nvarchar(255) NULL
,DESCR_1 nvarchar(255) NULL
,PRODUCT nvarchar(255) NULL
,JOURNAL_DATE nvarchar(255) NULL
,JOURNAL_ID nvarchar(255) NULL
,CURRENCY_CD nvarchar(255) NULL
,MONETARY_AMOUNT float
,FOREIGN_CURRENCY nvarchar(255) NULL
,AFFILIATE nvarchar(255) NULL
,PROJECT_ID nvarchar(255) NULL
,LINE_DESCR nvarchar(255) NULL
,DESCR254 nvarchar(255) NULL
,DOC_TYPE nvarchar(255) NULL
,DOC_SEQ_NBR nvarchar(255) NULL
,JRNL_LINE_SOURCE nvarchar(255) NULL
,JRNL_LN_REF nvarchar(255) NULL
,JOURNAL_LINE float NULL
,STATISTIC_AMOUNT float
,[PRIMARY] int IDENTITY(1,1) NOT NULL
)
on [PRIMARY]
go
Use /* insert your source database here*/
go

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
(AMOUNT_TYPE
,BUSINESS_UNIT
,LEDGER
,ACCOUNT
,DESCR
,DEPTID
,DESCR_1
,PRODUCT
,JOURNAL_DATE
,JOURNAL_ID
,CURRENCY_CD
,MONETARY_AMOUNT
,FOREIGN_CURRENCY
,AFFILIATE
,PROJECT_ID
,LINE_DESCR
,DESCR254
,DOC_TYPE
,DOC_SEQ_NBR
,JRNL_LINE_SOURCE
,JRNL_LN_REF
,JOURNAL_LINE
,STATISTIC_AMOUNT
)

-- this script will return lines with MONETARY_AMOUNT and insert them into Z_MIGRATION_TABLE in the new LHR_MIGRATION database.
-- Statistical amounts will be extracted separately

SELECT 'MONETARY' 'AMOUNT_TYPE', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT, C.DESCR, A.DEPTID, E.DESCR, A.PRODUCT, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, A.JOURNAL_ID, A.CURRENCY_CD, A.MONETARY_AMOUNT, A.FOREIGN_CURRENCY, A.AFFILIATE, A.PROJECT_ID, A.LINE_DESCR, B.DESCR254, A.DOC_TYPE, A.DOC_SEQ_NBR, A.JRNL_LINE_SOURCE, A.JRNL_LN_REF, A.JOURNAL_LINE, A.STATISTIC_AMOUNT  
  FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B, PS_GL_ACCOUNT_TBL C, XLATTABLE_VW D, PS_DEPT_TBL E  
  WHERE ( A.BUSINESS_UNIT = A1.BUSINESS_UNIT  
    AND A1.OPRID = 'BURAGOD'  
    AND ( A.BUSINESS_UNIT = B.BUSINESS_UNIT  
     AND A.JOURNAL_ID = B.JOURNAL_ID  
     AND A.JOURNAL_DATE = B.JOURNAL_DATE  
     AND A.UNPOST_SEQ = B.UNPOST_SEQ  
     AND C.ACCOUNT = A.ACCOUNT  
     AND C.EFFDT =  
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL C_ED  
        WHERE C.SETID = C_ED.SETID  
          AND C.ACCOUNT = C_ED.ACCOUNT  
          AND C_ED.EFFDT <= SYSDATE)  
     AND C.SETID = 'GLOBE'  
     AND D.EFFDT =  
        (SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED  
        WHERE D.FIELDNAME = D_ED.FIELDNAME  
          AND D.FIELDVALUE = D_ED.FIELDVALUE  
          AND D_ED.EFFDT <= SYSDATE)  
     AND D.FIELDNAME = 'JRNL_HDR_STATUS'  
     AND D.FIELDVALUE = B.JRNL_HDR_STATUS  
     AND E.DEPTID = A.DEPTID  
     AND E.EFFDT =  
        (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED  
        WHERE E.SETID = E_ED.SETID  
          AND E.DEPTID = E_ED.DEPTID  
          AND E_ED.EFFDT <= SYSDATE)  
     AND B.JRNL_HDR_STATUS IN ('P','U','T','N','V','D','E','Z')

    -- BUSINESS_UNIT to be merged with DEPTID to create the Hotel ID. Please add any missing ones the selection series below  
     AND B.BUSINESS_UNIT+'-'+A.DEPTID in 
     ('E0122-52588',    'E0123-52472',  'E0132-52473',  'E0132-52566',  'E0143-52474',  'E0143-54109',  'E0147-52475',  'E0147-54425',  'E0148-52476',  'E0149-52477',  'E0150-52471',  'E0150-52598',  'E0150-54105',  'E0150-54221',  'E0150-54380',  'E0150-54545',  'E0150-54547',  'E0150-54548',  'E0150-54750',  'E0150-54751',  'E0150-54752',  'E0150-54753',  'E0150-54759',  'E0150-54767',  'E0182-52468',  'E0182-52565',  'E0182-52599',  'E0182-52606',  'E0182-52607',  'E0182-52608',  'E0182-52609',  'E0182-52611',  'E0182-52612',  'E0182-52613',  'E0182-52634',  'E0182-53506',  'E0182-53512',  'E0182-53513',  'E0182-53514',  'E0182-53515',  'E0182-53520',  'E0182-53521',  'E0182-53522',  'E0182-53527',  'E0182-53528',  'E0182-53615',  'E0182-53616',  'E0182-53650',  'E0182-53691',  'E0182-53845',  'E0182-53847',  'E0182-53890',  'E0182-53936',  'E0182-54038',  'E0182-54054',  'E0182-54066',  'E0182-54072',  'E0182-54076',  'E0182-54080',  'E0182-54092',  'E0182-54096',  'E0182-54100',  'E0182-54104',  'E0182-54134',  'E0182-54140',  'E0182-54148',  'E0182-54166',  'E0182-54172',  'E0182-54242',  'E0182-54248',  'E0182-54286',  'E0182-54296',  'E0182-54297',  'E0182-54298',  'E0182-54301',  'E0182-54302',  'E0182-54316',  'E0182-54318',  'E0182-54322',  'E0182-54326',  'E0182-54350',  'E0182-54362',  'E0182-54364',  'E0182-54370',  'E0182-54376',  'E0182-54378',  'E0182-54381',  'E0182-54392',  'E0182-54422',  'E0182-54452',  'E0182-54470',  'E0182-54472',  'E0182-54494',  'E0182-54510',  'E0182-54540',  'E0182-54556',  'E0182-54590',  'E0182-54592',  'E0182-54610',  'E0182-54618',  'E0182-54644',  'E0182-54682',  'E0182-54724',  'E0182-54734',  'E0182-54741',  'E0182-54792',  'E0182-54824',  'E0182-54834',  'E0182-54870',  'E0182-54902',  'E0182-54946',  'E0183-52469',  'E0183-52600',  'E0183-54049',  'E0183-54133',  'E0183-54137',  'E0183-54283',  'E0183-54335',  'E0183-54379',  'E0183-54397',  'E0183-54529',  'E0183-54669',  'E0183-54727',  'E0183-54737',  'E0183-54784',  'E0184-52478',  'E0186-52479',  'E0186-53198',  'E0186-53199',  'E0190-52480',  'E0193-52555',  'E0221-52481',  'E0223-52482',  'E0229-52483',  'E0230-52484',  'E0230-52525',  'E0231-52485',  'E0231-52526',  'E0232-52486',  'E0232-52527',  'E0233-52487',  'E0233-52529',  'E0234-52488',  'E0234-52531',  'E0235-52489',  'E0235-52534',  'E0236-52490',  'E0236-52535',  'E0237-52491',  'E0237-52536',  'E0238-52492',  'E0238-52537',  'E0239-52493',  'E0239-52530',  'E0240-52494',  'E0240-52538',  'E0241-52495',  'E0241-52539',  'E0242-52496',  'E0242-52541',  'E0243-52497',  'E0243-52532',  'E0244-52498',  'E0244-52533',  'E0245-52499',  'E0245-52542',  'E0246-52500',  'E0246-52543',  'E0247-52503',  'E0247-52544',  'E0248-52504',  'E0248-52545',  'E0249-52505',  'E0249-52546',  'E0250-52507',  'E0250-52547',  'E0251-52508',  'E0251-52548',  'E0252-52509',  'E0252-52549',  'E0253-52510',  'E0253-52550',  'E0254-52511',  'E0254-52551',  'E0255-52512',  'E0255-52552',  'E0257-52513',  'E0257-52524',  'E0258-52514',  'E0258-52553',  'E0259-52515',  'E0259-52554',  'E0294-52516',  'E0360-52517',  'E0366-52518',  'E0366-54359',  'E0367-52519',  'E0367-54361',  'EMEAT-53643',  'EMEAT-53673',  'EMEAT-53724',  'EMEAT-58339')

    -- AND A.DEPTID='52469' --filter removed because it's merged with BUSINESS_UNIT to give the full Hotel ID in the filter directly above
     AND A.LEDGER = 'ACTUAL'  
    AND B.JOURNAL_DATE < TO_DATE('2016-09-30','YYYY-MM-DD') 
     ) )  
UNION  
SELECT 'MONETARY' 'AMOUNT_TYPE', F.BUSINESS_UNIT, F.LEDGER, F.ACCOUNT, H.DESCR, F.DEPTID, I.DESCR, F.PRODUCT, TO_CHAR(F.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, F.JOURNAL_ID, F.CURRENCY_CD, F.MONETARY_AMOUNT, F.FOREIGN_CURRENCY, F.AFFILIATE, F.PROJECT_ID, F.LINE_DESCR, G.DESCR254, F.DOC_TYPE, F.DOC_SEQ_NBR, F.JRNL_LINE_SOURCE, F.JRNL_LN_REF, F.JOURNAL_LINE, F.STATISTIC_AMOUNT 
  FROM PS_HH_JRNL_LN_ARC F, PS_HH_JRNL_HDR_ARC G, PS_GL_ACCOUNT_TBL H, PS_DEPT_TBL I  
  WHERE ( F.BUSINESS_UNIT = G.BUSINESS_UNIT  
     AND F.JOURNAL_ID = G.JOURNAL_ID  
     AND F.JOURNAL_DATE = G.JOURNAL_DATE  
     AND F.UNPOST_SEQ = G.UNPOST_SEQ  
     AND H.ACCOUNT = F.ACCOUNT  
     AND H.EFFDT =  
        (SELECT MAX(H_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL H_ED  
        WHERE H.SETID = H_ED.SETID  
          AND H.ACCOUNT = H_ED.ACCOUNT  
          AND H_ED.EFFDT <= SYSDATE)  
     AND I.DEPTID = F.DEPTID  
     AND I.EFFDT =  
        (SELECT MAX(I_ED.EFFDT) FROM PS_DEPT_TBL I_ED  
        WHERE I.SETID = I_ED.SETID  
          AND I.DEPTID = I_ED.DEPTID  
          AND I_ED.EFFDT <= SYSDATE)  
     AND H.SETID = 'GLOBE'  
     AND G.JRNL_HDR_STATUS IN ('P','U')  
     AND F.LEDGER = 'ACTUAL'  

     -- BUSINESS_UNIT to be merged with DEPTID to create the Hotel ID. Please add any missing ones the selection series below  
     AND G.BUSINESS_UNIT+'-'+F.DEPTID in ('E0122-52588',    'E0123-52472',  'E0132-52473',  'E0132-52566',  'E0143-52474',  'E0143-54109',  'E0147-52475',  'E0147-54425',  'E0148-52476',  'E0149-52477',  'E0150-52471',  'E0150-52598',  'E0150-54105',  'E0150-54221',  'E0150-54380',  'E0150-54545',  'E0150-54547',  'E0150-54548',  'E0150-54750',  'E0150-54751',  'E0150-54752',  'E0150-54753',  'E0150-54759',  'E0150-54767',  'E0182-52468',  'E0182-52565',  'E0182-52599',  'E0182-52606',  'E0182-52607',  'E0182-52608',  'E0182-52609',  'E0182-52611',  'E0182-52612',  'E0182-52613',  'E0182-52634',  'E0182-53506',  'E0182-53512',  'E0182-53513',  'E0182-53514',  'E0182-53515',  'E0182-53520',  'E0182-53521',  'E0182-53522',  'E0182-53527',  'E0182-53528',  'E0182-53615',  'E0182-53616',  'E0182-53650',  'E0182-53691',  'E0182-53845',  'E0182-53847',  'E0182-53890',  'E0182-53936',  'E0182-54038',  'E0182-54054',  'E0182-54066',  'E0182-54072',  'E0182-54076',  'E0182-54080',  'E0182-54092',  'E0182-54096',  'E0182-54100',  'E0182-54104',  'E0182-54134',  'E0182-54140',  'E0182-54148',  'E0182-54166',  'E0182-54172',  'E0182-54242',  'E0182-54248',  'E0182-54286',  'E0182-54296',  'E0182-54297',  'E0182-54298',  'E0182-54301',  'E0182-54302',  'E0182-54316',  'E0182-54318',  'E0182-54322',  'E0182-54326',  'E0182-54350',  'E0182-54362',  'E0182-54364',  'E0182-54370',  'E0182-54376',  'E0182-54378',  'E0182-54381',  'E0182-54392',  'E0182-54422',  'E0182-54452',  'E0182-54470',  'E0182-54472',  'E0182-54494',  'E0182-54510',  'E0182-54540',  'E0182-54556',  'E0182-54590',  'E0182-54592',  'E0182-54610',  'E0182-54618',  'E0182-54644',  'E0182-54682',  'E0182-54724',  'E0182-54734',  'E0182-54741',  'E0182-54792',  'E0182-54824',  'E0182-54834',  'E0182-54870',  'E0182-54902',  'E0182-54946',  'E0183-52469',  'E0183-52600',  'E0183-54049',  'E0183-54133',  'E0183-54137',  'E0183-54283',  'E0183-54335',  'E0183-54379',  'E0183-54397',  'E0183-54529',  'E0183-54669',  'E0183-54727',  'E0183-54737',  'E0183-54784',  'E0184-52478',  'E0186-52479',  'E0186-53198',  'E0186-53199',  'E0190-52480',  'E0193-52555',  'E0221-52481',  'E0223-52482',  'E0229-52483',  'E0230-52484',  'E0230-52525',  'E0231-52485',  'E0231-52526',  'E0232-52486',  'E0232-52527',  'E0233-52487',  'E0233-52529',  'E0234-52488',  'E0234-52531',  'E0235-52489',  'E0235-52534',  'E0236-52490',  'E0236-52535',  'E0237-52491',  'E0237-52536',  'E0238-52492',  'E0238-52537',  'E0239-52493',  'E0239-52530',  'E0240-52494',  'E0240-52538',  'E0241-52495',  'E0241-52539',  'E0242-52496',  'E0242-52541',  'E0243-52497',  'E0243-52532',  'E0244-52498',  'E0244-52533',  'E0245-52499',  'E0245-52542',  'E0246-52500',  'E0246-52543',  'E0247-52503',  'E0247-52544',  'E0248-52504',  'E0248-52545',  'E0249-52505',  'E0249-52546',  'E0250-52507',  'E0250-52547',  'E0251-52508',  'E0251-52548',  'E0252-52509',  'E0252-52549',  'E0253-52510',  'E0253-52550',  'E0254-52511',  'E0254-52551',  'E0255-52512',  'E0255-52552',  'E0257-52513',  'E0257-52524',  'E0258-52514',  'E0258-52553',  'E0259-52515',  'E0259-52554',  'E0294-52516',  'E0360-52517',  'E0366-52518',  'E0366-54359',  'E0367-52519',  'E0367-54361',  'EMEAT-53643',  'EMEAT-53673',  'EMEAT-53724',  'EMEAT-58339') 
    -- AND F.DEPTID='52469' --filter removed because it's merged with BUSINESS_UNIT to give the full Hotel ID in the filter directly above
     AND G.JOURNAL_DATE < TO_DATE('2016-09-30','YYYY-MM-DD') ) 
go

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
(AMOUNT_TYPE
,BUSINESS_UNIT
,LEDGER
,ACCOUNT
,DESCR
,DEPTID
,DESCR_1
,PRODUCT
,JOURNAL_DATE
,JOURNAL_ID
,CURRENCY_CD
,MONETARY_AMOUNT
,FOREIGN_CURRENCY
,AFFILIATE
,PROJECT_ID
,LINE_DESCR
,DESCR254
,DOC_TYPE
,DOC_SEQ_NBR
,JRNL_LINE_SOURCE
,JRNL_LN_REF
,JOURNAL_LINE
,STATISTIC_AMOUNT
)

-- this script will return lines with STATISTICAL_AMOUNT and insert them into Z_MIGRATION_TABLE in the new LHR_MIGRATION database.

SELECT 'STATISTIC' 'AMOUNT_TYPE', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT, C.DESCR, A.DEPTID, E.DESCR, A.PRODUCT, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, A.JOURNAL_ID, A.CURRENCY_CD, A.MONETARY_AMOUNT, A.FOREIGN_CURRENCY, A.AFFILIATE, A.PROJECT_ID, A.LINE_DESCR, B.DESCR254, A.DOC_TYPE, A.DOC_SEQ_NBR, A.JRNL_LINE_SOURCE, A.JRNL_LN_REF, A.JOURNAL_LINE, A.STATISTIC_AMOUNT 

  FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B, PS_GL_ACCOUNT_TBL C, XLATTABLE_VW D, PS_DEPT_TBL E 
  WHERE ( A.BUSINESS_UNIT = A1.BUSINESS_UNIT 
    AND A1.OPRID = 'BURAGOD' 
    AND ( A.BUSINESS_UNIT = B.BUSINESS_UNIT 
     AND A.JOURNAL_ID = B.JOURNAL_ID 
     AND A.JOURNAL_DATE = B.JOURNAL_DATE 
     AND A.UNPOST_SEQ = B.UNPOST_SEQ 
     AND C.ACCOUNT = A.ACCOUNT 
     AND C.EFFDT = 
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL C_ED 
        WHERE C.SETID = C_ED.SETID 
          AND C.ACCOUNT = C_ED.ACCOUNT 
          AND C_ED.EFFDT <= SYSDATE) 
     AND C.SETID = 'GLOBE' 
     AND D.EFFDT = 
        (SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED 
        WHERE D.FIELDNAME = D_ED.FIELDNAME 
          AND D.FIELDVALUE = D_ED.FIELDVALUE 
          AND D_ED.EFFDT <= SYSDATE) 
     AND D.FIELDNAME = 'JRNL_HDR_STATUS' 
     AND D.FIELDVALUE = B.JRNL_HDR_STATUS 
     AND E.DEPTID = A.DEPTID 
     AND E.EFFDT = 
        (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED 
        WHERE E.SETID = E_ED.SETID 
          AND E.DEPTID = E_ED.DEPTID 
          AND E_ED.EFFDT <= SYSDATE) 
     AND B.JRNL_HDR_STATUS IN ('P','U','T','N','V','D','E','Z') 
     --AND B.BUSINESS_UNIT = 'E0132' 
     --AND A.DEPTID='52566'
     AND B.BUSINESS_UNIT+'-'+A.DEPTID in ('E0122-52588',    'E0123-52472',  'E0132-52473',  'E0132-52566',  'E0143-52474',  'E0143-54109',  'E0147-52475',  'E0147-54425',  'E0148-52476',  'E0149-52477',  'E0150-52471',  'E0150-52598',  'E0150-54105',  'E0150-54221',  'E0150-54380',  'E0150-54545',  'E0150-54547',  'E0150-54548',  'E0150-54750',  'E0150-54751',  'E0150-54752',  'E0150-54753',  'E0150-54759',  'E0150-54767',  'E0182-52468',  'E0182-52565',  'E0182-52599',  'E0182-52606',  'E0182-52607',  'E0182-52608',  'E0182-52609',  'E0182-52611',  'E0182-52612',  'E0182-52613',  'E0182-52634',  'E0182-53506',  'E0182-53512',  'E0182-53513',  'E0182-53514',  'E0182-53515',  'E0182-53520',  'E0182-53521',  'E0182-53522',  'E0182-53527',  'E0182-53528',  'E0182-53615',  'E0182-53616',  'E0182-53650',  'E0182-53691',  'E0182-53845',  'E0182-53847',  'E0182-53890',  'E0182-53936',  'E0182-54038',  'E0182-54054',  'E0182-54066',  'E0182-54072',  'E0182-54076',  'E0182-54080',  'E0182-54092',  'E0182-54096',  'E0182-54100',  'E0182-54104',  'E0182-54134',  'E0182-54140',  'E0182-54148',  'E0182-54166',  'E0182-54172',  'E0182-54242',  'E0182-54248',  'E0182-54286',  'E0182-54296',  'E0182-54297',  'E0182-54298',  'E0182-54301',  'E0182-54302',  'E0182-54316',  'E0182-54318',  'E0182-54322',  'E0182-54326',  'E0182-54350',  'E0182-54362',  'E0182-54364',  'E0182-54370',  'E0182-54376',  'E0182-54378',  'E0182-54381',  'E0182-54392',  'E0182-54422',  'E0182-54452',  'E0182-54470',  'E0182-54472',  'E0182-54494',  'E0182-54510',  'E0182-54540',  'E0182-54556',  'E0182-54590',  'E0182-54592',  'E0182-54610',  'E0182-54618',  'E0182-54644',  'E0182-54682',  'E0182-54724',  'E0182-54734',  'E0182-54741',  'E0182-54792',  'E0182-54824',  'E0182-54834',  'E0182-54870',  'E0182-54902',  'E0182-54946',  'E0183-52469',  'E0183-52600',  'E0183-54049',  'E0183-54133',  'E0183-54137',  'E0183-54283',  'E0183-54335',  'E0183-54379',  'E0183-54397',  'E0183-54529',  'E0183-54669',  'E0183-54727',  'E0183-54737',  'E0183-54784',  'E0184-52478',  'E0186-52479',  'E0186-53198',  'E0186-53199',  'E0190-52480',  'E0193-52555',  'E0221-52481',  'E0223-52482',  'E0229-52483',  'E0230-52484',  'E0230-52525',  'E0231-52485',  'E0231-52526',  'E0232-52486',  'E0232-52527',  'E0233-52487',  'E0233-52529',  'E0234-52488',  'E0234-52531',  'E0235-52489',  'E0235-52534',  'E0236-52490',  'E0236-52535',  'E0237-52491',  'E0237-52536',  'E0238-52492',  'E0238-52537',  'E0239-52493',  'E0239-52530',  'E0240-52494',  'E0240-52538',  'E0241-52495',  'E0241-52539',  'E0242-52496',  'E0242-52541',  'E0243-52497',  'E0243-52532',  'E0244-52498',  'E0244-52533',  'E0245-52499',  'E0245-52542',  'E0246-52500',  'E0246-52543',  'E0247-52503',  'E0247-52544',  'E0248-52504',  'E0248-52545',  'E0249-52505',  'E0249-52546',  'E0250-52507',  'E0250-52547',  'E0251-52508',  'E0251-52548',  'E0252-52509',  'E0252-52549',  'E0253-52510',  'E0253-52550',  'E0254-52511',  'E0254-52551',  'E0255-52512',  'E0255-52552',  'E0257-52513',  'E0257-52524',  'E0258-52514',  'E0258-52553',  'E0259-52515',  'E0259-52554',  'E0294-52516',  'E0360-52517',  'E0366-52518',  'E0366-54359',  'E0367-52519',  'E0367-54361',  'EMEAT-53643',  'EMEAT-53673',  'EMEAT-53724',  'EMEAT-58339') 
     AND A.LEDGER = 'ACTUAL' 
     --AND B.JOURNAL_DATE BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-01-31','YYYY-MM-DD') 
     AND A.ACCOUNT BETWEEN '900000' AND '999999' ))  
UNION 
SELECT 'STATISTIC' 'AMOUNT_TYPE', F.BUSINESS_UNIT, F.LEDGER, F.ACCOUNT, H.DESCR, F.DEPTID, I.DESCR, F.PRODUCT, TO_CHAR(F.JOURNAL_DATE,'YYYY-MM-DD') JOURNAL_DATE, F.JOURNAL_ID, F.CURRENCY_CD, F.MONETARY_AMOUNT, F.FOREIGN_CURRENCY, F.AFFILIATE, F.PROJECT_ID, F.LINE_DESCR, G.DESCR254, F.DOC_TYPE, F.DOC_SEQ_NBR, F.JRNL_LINE_SOURCE, F.JRNL_LN_REF, F.JOURNAL_LINE, F.STATISTIC_AMOUNT 
  FROM PS_HH_JRNL_LN_ARC F, PS_HH_JRNL_HDR_ARC G, PS_GL_ACCOUNT_TBL H, PS_DEPT_TBL I 
  WHERE ( F.BUSINESS_UNIT = G.BUSINESS_UNIT 
     AND F.JOURNAL_ID = G.JOURNAL_ID 
     AND F.JOURNAL_DATE = G.JOURNAL_DATE 
     AND F.UNPOST_SEQ = G.UNPOST_SEQ 
     AND H.ACCOUNT = F.ACCOUNT 
     AND H.EFFDT = 
        (SELECT MAX(H_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL H_ED 
        WHERE H.SETID = H_ED.SETID 
          AND H.ACCOUNT = H_ED.ACCOUNT 
          AND H_ED.EFFDT <= SYSDATE) 
     AND I.DEPTID = F.DEPTID 
     AND I.EFFDT = 
        (SELECT MAX(I_ED.EFFDT) FROM PS_DEPT_TBL I_ED 
        WHERE I.SETID = I_ED.SETID 
          AND I.DEPTID = I_ED.DEPTID 
          AND I_ED.EFFDT <= SYSDATE) 
     AND H.SETID = 'GLOBE' 
     AND G.JRNL_HDR_STATUS IN ('P','U') 
     AND F.LEDGER = 'ACTUAL' 
     --AND G.BUSINESS_UNIT = 'E0132' 
     --AND F.DEPTID='52566'
     --AND G.JOURNAL_DATE BETWEEN TO_DATE('2015-01-01','YYYY-MM-DD') AND TO_DATE('2015-01-31','YYYY-MM-DD') 
     AND B.BUSINESS_UNIT+'-'+A.DEPTID in ('E0122-52588',    'E0123-52472',  'E0132-52473',  'E0132-52566',  'E0143-52474',  'E0143-54109',  'E0147-52475',  'E0147-54425',  'E0148-52476',  'E0149-52477',  'E0150-52471',  'E0150-52598',  'E0150-54105',  'E0150-54221',  'E0150-54380',  'E0150-54545',  'E0150-54547',  'E0150-54548',  'E0150-54750',  'E0150-54751',  'E0150-54752',  'E0150-54753',  'E0150-54759',  'E0150-54767',  'E0182-52468',  'E0182-52565',  'E0182-52599',  'E0182-52606',  'E0182-52607',  'E0182-52608',  'E0182-52609',  'E0182-52611',  'E0182-52612',  'E0182-52613',  'E0182-52634',  'E0182-53506',  'E0182-53512',  'E0182-53513',  'E0182-53514',  'E0182-53515',  'E0182-53520',  'E0182-53521',  'E0182-53522',  'E0182-53527',  'E0182-53528',  'E0182-53615',  'E0182-53616',  'E0182-53650',  'E0182-53691',  'E0182-53845',  'E0182-53847',  'E0182-53890',  'E0182-53936',  'E0182-54038',  'E0182-54054',  'E0182-54066',  'E0182-54072',  'E0182-54076',  'E0182-54080',  'E0182-54092',  'E0182-54096',  'E0182-54100',  'E0182-54104',  'E0182-54134',  'E0182-54140',  'E0182-54148',  'E0182-54166',  'E0182-54172',  'E0182-54242',  'E0182-54248',  'E0182-54286',  'E0182-54296',  'E0182-54297',  'E0182-54298',  'E0182-54301',  'E0182-54302',  'E0182-54316',  'E0182-54318',  'E0182-54322',  'E0182-54326',  'E0182-54350',  'E0182-54362',  'E0182-54364',  'E0182-54370',  'E0182-54376',  'E0182-54378',  'E0182-54381',  'E0182-54392',  'E0182-54422',  'E0182-54452',  'E0182-54470',  'E0182-54472',  'E0182-54494',  'E0182-54510',  'E0182-54540',  'E0182-54556',  'E0182-54590',  'E0182-54592',  'E0182-54610',  'E0182-54618',  'E0182-54644',  'E0182-54682',  'E0182-54724',  'E0182-54734',  'E0182-54741',  'E0182-54792',  'E0182-54824',  'E0182-54834',  'E0182-54870',  'E0182-54902',  'E0182-54946',  'E0183-52469',  'E0183-52600',  'E0183-54049',  'E0183-54133',  'E0183-54137',  'E0183-54283',  'E0183-54335',  'E0183-54379',  'E0183-54397',  'E0183-54529',  'E0183-54669',  'E0183-54727',  'E0183-54737',  'E0183-54784',  'E0184-52478',  'E0186-52479',  'E0186-53198',  'E0186-53199',  'E0190-52480',  'E0193-52555',  'E0221-52481',  'E0223-52482',  'E0229-52483',  'E0230-52484',  'E0230-52525',  'E0231-52485',  'E0231-52526',  'E0232-52486',  'E0232-52527',  'E0233-52487',  'E0233-52529',  'E0234-52488',  'E0234-52531',  'E0235-52489',  'E0235-52534',  'E0236-52490',  'E0236-52535',  'E0237-52491',  'E0237-52536',  'E0238-52492',  'E0238-52537',  'E0239-52493',  'E0239-52530',  'E0240-52494',  'E0240-52538',  'E0241-52495',  'E0241-52539',  'E0242-52496',  'E0242-52541',  'E0243-52497',  'E0243-52532',  'E0244-52498',  'E0244-52533',  'E0245-52499',  'E0245-52542',  'E0246-52500',  'E0246-52543',  'E0247-52503',  'E0247-52544',  'E0248-52504',  'E0248-52545',  'E0249-52505',  'E0249-52546',  'E0250-52507',  'E0250-52547',  'E0251-52508',  'E0251-52548',  'E0252-52509',  'E0252-52549',  'E0253-52510',  'E0253-52550',  'E0254-52511',  'E0254-52551',  'E0255-52512',  'E0255-52552',  'E0257-52513',  'E0257-52524',  'E0258-52514',  'E0258-52553',  'E0259-52515',  'E0259-52554',  'E0294-52516',  'E0360-52517',  'E0366-52518',  'E0366-54359',  'E0367-52519',  'E0367-54361',  'EMEAT-53643',  'EMEAT-53673',  'EMEAT-53724',  'EMEAT-58339') 
     AND F.ACCOUNT BETWEEN '900000' AND '999999')
go
3
  • Yes, it can. What is your question? What problem are you having with doing so?
    – Philᵀᴹ
    Commented Oct 31, 2016 at 15:51
  • I want to know if I need to create the database first and then create the linked servers to perform the data extract from Oracle to sql. Also should both the servers be on the same domain? Thanks! Commented Oct 31, 2016 at 15:53
  • Where do I keep the "open tran" snippet? Can I keep that in the use/* source database */ ? Please advice. Thanks! Commented Nov 1, 2016 at 3:37

1 Answer 1

0

Yes you can use linked servers, that is one way we handle this. You will need the correct oracle driver installed in order to use it. I would put the opentran on the inserting of the data versus any checking or auditing of the incoming data. As the oracle db is remote you can't include it in the transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.