extract yesterdays info without failures.

Question

Post reply

extract yesterdays info without failures.

bluepaperbag

SSCrazy

Points: 2055
More actions
October 24, 2007 at 9:53 am

#183105

I like to extract yesterday's data daily.
SIDATE = The field for incrmental loading. I want to know if there is anything wrong with my code.
datawarehousecontrol = table where I have columns successfullextractdate and currentextractdate
datawareshousecontrolrecords = table where I have the history of how many records is inserted.
successfullextractdate should'nt be updated if the extracttion fails. Is this right? I think there is something wrong but i cant figure it out and its hard to test it as I can't go back in time.
BEGIN TRAN
DECLARE @LastSuccessfullExtractDate DATETIME,
@ERROR INT,
@LSED DATETIME,
@ROWCOUNT INT,
@CurrentExtractDate DATETIME,
@CED DATETIME
SELECT @LastSuccessfullExtractDate = LastSuccessfullExtractDate
FROM dataWarehouseControl
SELECT @CurrentExtractDate = CurrentExtractDate
FROM dataWarehouseControl
/*
delete DATAWAREHOUSECONTROLrecords
where recordsinserted = 0
UPDATE DATAWAREHOUSECONTROL
SET LastSuccessfullExtractDate = '2007-10-16',
CurrentExtractDate = '2007-10-17'
SELECT * FROM DATAWAREHOUSECONTROL
SELECT * FROM DATAWAREHOUSECONTROLRECORDS
TRUNCATE TABLE DATAWAREHOUSECONTROLRECORDS
TRUNCATE TABLE SHPHST3
SELECT COUNT(*) FROM SHPHST3
SELECT * FROM IPGDB..SHPHST
WHERE SIDATE > '2007-10-16'
AND SIDATE <= '2007-10-17'
SELECT COUNT (*) FROM IPGDB..SHPHST
WHERE SIDATE = '2007-10-23'
SELECT COUNT (*) FROM IPGDB..SHPHST
WHERE SIDATE = '2007-10-24'
SELECT * FROM DATAWAREHOUSECONTROLRECORDS
*/
--SET @LSED = GETDATE()-1
--SET @CED = GETDATE()
-- 17, 18
SET @LSED = '2007-10-21'
SET @CED = '2007-10-22'
-- SET@LSED = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME)
-- select COUNT(SIDATE) FROM SHPHST2
-- select COUNT(SIDATE) FROM SHPHST
-- TRUNCATE TABLE SHPHST2
UPDATE IPGDB_UPDATE..dataWarehouseControl
SET CurrentExtractDate = @CED
IF @@ERROR <> 0 SELECT @ERROR = @@ERROR
UPDATE IPGDB_UPDATE..dataWarehouseControl
SET LastSuccessfullExtractDate = @LSED
IF @@ERROR <> 0 SELECT @ERROR = @@ERROR
INSERT IPGDB_UPDATE..SHPHST3
select *
FROM IPGDB..SHPHST
where SIDATE > @LastSuccessfullExtractDate
AND SIDATE <= @CurrentExtractDate
SELECT @ROWCOUNT = @@ROWCOUNT
IF @@ERROR <> 0 SELECT @ERROR = @@ERROR
INSERT dataWarehouseControlRecords
SELECT 'SHPHST3', @ROWCOUNT, @LSED, @CED
IF @@ERROR <> 0 SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END

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

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

bluepaperbag SSCrazy Points: 2055 More actions · Answer 1

Read article all about transactions and it helped me alot.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 2

I'm thinking this is wrong...

SELECT * FROM IPGDB..SHPHST

WHERE SIDATE > '2007-10-16'

AND SIDATE <= '2007-10-17'

Should be...

SELECT * FROM IPGDB..SHPHST

WHERE SIDATE >= '2007-10-16'

AND SIDATE < '2007-10-17'

Other than that, your transaction is pretty long and you might want to look into things that you can do to shorten it up... anything that's not absolutely needed to maintain the ACID qualities should be moved outside the transaction, for example...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)