inserting rows without cursor

  • Hi,

    I need to retrieve rows from a MSSQL 2008 table and insert them into an Oracle database using a linked server. Status of the insert transaction has to be updated in the source SQL table of each rows. So far, I have:

    CREATE TABLE #SAP

    (

    MANDT VARCHAR(3),

    NoBatch VARCHAR(15),

    NoLigne INT,

    [Date] VARCHAR(8),

    [Time] VARCHAR(8),

    Statut VARCHAR(1)

    )

    INSERT INTO #SAP

    SELECT '031' AS MANDT, NoBatch, NoLigne,

    CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + REPLACE(STR(DATEPART(MONTH, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(DAY, GETDATE()), 2, 0), ' ', '0') AS [Date],

    REPLACE(STR(DATEPART(HOUR, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(MINUTE, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(SECOND, GETDATE()), 2, 0), ' ', '0') AS [Time],

    ' ' AS Statut

    FROM dbo.TB_psapProductionSAP

    WHERE Treated = 0

    I'm getting all rows not yet treated to be inserted into Oracle and put them into a temp table. To insert a single row, I have:

    BEGIN TRY

    INSERT INTO OPENQUERY(LQP,'SELECT * FROM SAPLQP.ZMARKPACK')

    VALUES

    (

    '031',

    @Batch,

    @NoLigne,

    CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + REPLACE(STR(DATEPART(MONTH, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(DAY, GETDATE()), 2, 0), ' ', '0'),

    REPLACE(STR(DATEPART(HOUR, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(MINUTE, GETDATE()), 2, 0), ' ', '0') + REPLACE(STR(DATEPART(SECOND, GETDATE()), 2, 0), ' ', '0'),

    ' '

    )

    END TRY

    BEGIN CATCH

    SELECT @ErrorMsg = ERROR_MESSAGE()

    SET @status = 'F'

    END CATCH

    INSERT INTO dbo.TB_sapStatus (

    cmdBatchNo,

    sapStatus,

    sapDateHeure,

    sapMessage

    ) VALUES (

    /* cmdBatchNo - varchar(15) */ @Batch,

    /* sapStatus - varchar(1) */ @status,

    /* sapDateHeure - smalldatetime */ GETDATE(),

    /* sapMessage - varchar(100) */ @ErrorMsg )

    How can I do something like this without using cursor and looping throught each rows in the temp table ?

    thanks a lot for your time and help

  • Honestly, since you have to mark each row for success/failure, a cursor will probably be your best bet.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • If you need to log the status for each row then you are stuck with a single row insert so there's not much you can do about it.

    If you could do it in batches then you could create a file and load that using an oracle loader or SSIS (that will be slower probably as it needs to use the available connection managers).

    If performance isn't an issue or you really need to log each insert then you probably need to stick with looping through the temp table. Doesn't need an explicit cursor but it would have to be something equivalent.


    Cursors never.
    DTS - only when needed and never to control.

  • yes, I need to log every rows inserted with the status. So I guess I'll have to stick with cursors.

    performance isn't really an issue.

    thanks a lot for your replies

Viewing 4 posts - 1 through 3 (of 3 total)

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