0

I am working on a task where some tables from Oracle Peoplesoft have to pulled into SQL 2014. The concerned person gave me the following code and I see that in the end he mentions the comment Use /* Insert your source database here */. I am assuming the openrow set query should be used in place of the comments. Can someone please clarify if this is the way ahead. Pasting some of the code here for your reference

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
)
0

The USE command changes the database context to the specified database or database snapshot in SQL Server. I believe there should be a query with matching columns after your INSERT statement that would be the source for the INSERT. It may be OPENQUERY and could look like the following:

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
    )
SELECT 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
FROM OPENQUERY(LinkServerName,'SELECT 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
    FROM SourceTableName');

LinkedServerName would be the real linked server name and SourceTableName would be the name of the table in the linked server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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