Database

Mycroft Holmes wrote:

and the dba/developers/coder/us lazy bastards do not need to test for null values.

jschell wrote:

since zero and null are not necessarily the same thing

Mycroft Holmes wrote:

our staging table are exact replicas of the source data, this allows us to use
Bulk Copy when loading the data. It also insures there are no errors in the Load
part of the ELT process.

Mycroft Holmes wrote:

The second greatest scam

Mycroft Holmes wrote:

Extract - Load - Transform simplifies and speeds up the entire process.

USE AdventureWorks2008R2;
GO

-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError
DECLARE @ErrorLogID INT;

BEGIN TRY
    BEGIN TRANSACTION;

    -- A FOREIGN KEY constraint exists on this table. This
    -- statement will generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Call procedure to print error information.
    EXECUTE dbo.uspPrintError;

    -- Roll back any active or uncommittable transactions before
    -- inserting information in the ErrorLog.
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;

-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO