Stored procedure to replace null's in FK on fact table

Question

I have built a fact table in a staging area, where some of the FK (the BK from dimensions) are NULL.

During the ETL in SSIS, I have used a derived column to replace all NULL's with the value "-1".

The problem is that I have 15 columns and it takes a long time to populate the entire table.

Is there a stored procedure that I can use to avoid this? Or any other method that reduces this time?

Answer 1

I did it doing the following stored procedure:

CREATE PROCEDURE [dbo].[Inferred_FACT_TABLE] 
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN
        UPDATE [dbo].[FACT_STG_cenas]
        SET [FK_Alpha] = -1
        WHERE [FK_Alpha] IS NULL

        UPDATE [dbo].[FACT_STG_cenas]
        SET [FK_Bravo] = -1
        WHERE [FK_Bravo] IS NULL

        UPDATE [dbo].[FACT_STG_cenas]
        SET [FK_Lima] = -1
        WHERE [FK_Lima] IS NULL

        UPDATE [dbo].[FACT_STG_cenas]
        SET [FK_Charlie] = -1
        WHERE [FK_Charlie] IS NULL

        UPDATE [dbo].[FACT_STG_cenas]
        SET [FK_Mike] = -1
        WHERE [FK_Mike] IS NULL

    
    END
END