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

Insert Items with OPenquery Statment

I am linking to an Oracle server via my sql server and need to insert data into the oracle server
This is the data I need to put in the openquery statement where the statment has tripple xxx's.  But I do get how to get that data in there.
Select      ActualVal,
            TargetVal,
            ToleranceVal,
            Bench1,
            Bench2,
            Bench3,
            Bench4,
            Bench5
From      CognosMetrics where bitDB = 1
INSERT OPENQUERY(METRICS, 'INSERT INTO KPI_USER_VALUE (KPI_USER_VALUE_SID,
                            KPI_SID,
                            TIME_PERIOD_SID,
                            UNIT_LOOKUP_ID,
                            ACTUAL_VALUE,
                            TARGET_VALUE,
                            TOLERANCE_RAW,
                            BENCHMARK_VALUE,
                            BENCHMARK2_VALUE,
                            BENCHMARK3_VALUE,
                            BENCHMARK4_VALUE,
                            BENCHMARK5_VALUE,
                            SECURITY_ITEM_SID,
                            CREATED_DT,
                            PROCESSED)
SELECT   
(select max(kpi_sid) + 1 from (select kpi_sid from kpi union select kpi_user_value_sid from kpi_user_value)) as kpi_sid, KPI_SID, 'xxxTIME_PERIOD_SID', KCC.UNIT_LOOKUP_ID, 'xxxACTUAL_VALUE', 'xxxTARGET_VALUE', 'xxxTOLERANCE_RAW', 'xxxBENCHMARK_VALUE', 'xxxBENCHMARK2_VALUE', 'xxxBENCHMARK3_VALUE', 'xxxBENCHMARK4_VALUE', 'xxxBENCHMARK5_VALUE', 21, sysdate, 'N'
     FROM      KPI KPI
            INNER JOIN
               KPI_CLASS_CURRENCY KCC
            ON KPI.KPI_CLASS_SID = KCC.KPI_CLASS_SID
               AND KCC.CURRENCY_CD = 'USD'
    WHERE   KPI.KPI_SID = 9573
	')

Open in new window

0
kdeutsch
Asked:
kdeutsch
  • 2
1 Solution
 
Máté FarkasDatabase Developer and AdministratorCommented:
You should use simple SELECT * FROM ... query in the OPENQUERY statement. SQL Server will map the "remote insert" operator thow this select statement. For example:
INSERT INTO OPENQUERY(METRICS, 'INSERT * FROM KPI_USER_VALUE')(KPI_USER_VALUE_SID, KPI_SID)
SELECT kpi_user_value_sid, kpi_sid FROM KPI

--OR

INSERT INTO OPENQUERY(METRICS, 'INSERT * FROM KPI_USER_VALUE')(KPI_USER_VALUE_SID, KPI_SID)
VALUES(123, 321)

Open in new window

0
 
Máté FarkasDatabase Developer and AdministratorCommented:
Sorry
INSERT INTO OPENQUERY(METRICS, SELECT * FROM KPI_USER_VALUE')(KPI_USER_VALUE_SID, KPI_SID)
SELECT kpi_user_value_sid, kpi_sid FROM KPI

--OR

INSERT INTO OPENQUERY(METRICS, 'SELECT * FROM KPI_USER_VALUE')(KPI_USER_VALUE_SID, KPI_SID)
VALUES(123, 321)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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