November 18, 2010 at 9:13 am
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
November 18, 2010 at 9:16 am
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
November 18, 2010 at 9:32 am
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.
November 18, 2010 at 11:09 am
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