✅ 4. dbo.InsertFromStagingToFinalTable




USE [DB_Ejraeiyaat2025]

GO

/****** Object:  StoredProcedure [dbo].[InsertFromStagingToFinalTable]    Script Date: 19/02/1404 01:19:31 ق.ظ ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[InsertFromStagingToFinalTable]

    @FieldsStatement NVARCHAR(MAX),

    @INTOTable NVARCHAR(255),

    @FROMTable NVARCHAR(255),

    @WHEREStatement NVARCHAR(MAX) = ''

AS

BEGIN

    SET NOCOUNT ON;





    DECLARE @SQL NVARCHAR(MAX);

    DECLARE @RowsInserted INT;

    DECLARE @CountSQL NVARCHAR(MAX);

    DECLARE @TotalCount INT;

	DECLARE @StartTime DATETIME2 = SYSDATETIME();





    BEGIN TRY

        IF OBJECT_ID(@INTOTable, 'U') IS NOT NULL

        BEGIN

            SET @SQL = '

                DECLARE @RC INT;





                INSERT INTO ' + QUOTENAME(@INTOTable) + --' (' + @FieldsStatement + ')' +

                'SELECT ' + @FieldsStatement + '

                FROM ' + QUOTENAME(@FROMTable) + ' ' + @WHEREStatement + ';





                SET @RC = @@ROWCOUNT;

                SELECT @RC AS InsertedCount;';

        END

        ELSE

        BEGIN

            SET @SQL = '

                DECLARE @RC INT;





                SELECT ' + @FieldsStatement + '

                INTO ' + QUOTENAME(@INTOTable) + '

                FROM ' + QUOTENAME(@FROMTable) + ' ' + @WHEREStatement + ';





                SET @RC = @@ROWCOUNT;

                SELECT @RC AS InsertedCount;';

        END





        -- اجرای کوئری و گرفتن نتیجه

        CREATE TABLE #TempRowCount(InsertedCount INT);

        INSERT INTO #TempRowCount

        EXEC (@SQL);





        SELECT @RowsInserted = InsertedCount FROM #TempRowCount;

        DROP TABLE #TempRowCount;





        SET @CountSQL = 'SELECT @TotalCountOut = COUNT(*) FROM ' + QUOTENAME(@INTOTable);





        EXEC sp_executesql

            @CountSQL,

            N'@TotalCountOut INT OUTPUT',

            @TotalCountOut = @TotalCount OUTPUT;









        IF OBJECT_ID(@FROMTable, 'U') IS NOT NULL

        BEGIN

            SET @SQL = 'DROP TABLE ' + QUOTENAME(@FROMTable) + ';';

            EXEC sp_executesql @SQL;

        END





		DECLARE @EndTime DATETIME2	= SYSDATETIME();

		DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);









		--PRINT '';

		--PRINT '';

        PRINT '✅ Insert completed successfully. Insert From ' + @FROMTable + ' INTO ' + @INTOTable;

        PRINT '➡️ Records inserted: ' + CAST(@RowsInserted AS NVARCHAR);

		PRINT '➡️ All records in [' + @INTOTable + ']: ' + CAST(@TotalCount AS NVARCHAR);

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

    END TRY

    BEGIN CATCH

		--PRINT '';

		--PRINT '';

        PRINT '❌ Error occurred during insert into ' + @INTOTable;

		PRINT '⏱ Time elapsed: ' + CAST(@ElapsedSeconds AS NVARCHAR) + ' seconds';

        THROW;

    END CATCH

END