USE [triovisttrade]
GO
/****** Object:  StoredProcedure [mp].[OstatkiData_21vek_pbi]    Script Date: 30.06.2025 20:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [mp].[OstatkiData_21vek_pbi]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE 
        @fbo_path NVARCHAR(500) = N'D:\power bi\Python data\ozon_data\ostatki_21vek\fbo\',
        @fbs_path NVARCHAR(500) = N'D:\power bi\Python data\ozon_data\ostatki_21vek\fbs\',
        @today CHAR(8) = FORMAT(GETDATE(), 'ddMMyyyy'),
        @yesterday CHAR(8) = FORMAT(DATEADD(DAY, -1, GETDATE()), 'ddMMyyyy'),
        @sql NVARCHAR(MAX),
        @file_path NVARCHAR(500),
        @file_date DATE;

	SET @file_date = TRY_CONVERT(DATE, RIGHT(@today, 4) + SUBSTRING(@today, 3, 2) + LEFT(@today, 2));
    DELETE FROM triovisttrade.ozon.fbo_ostatki WHERE load_date = @file_date;
    DELETE FROM triovisttrade.ozon.fbs_ostatki WHERE load_date = @file_date;

    SET @file_date = TRY_CONVERT(DATE, RIGHT(@yesterday, 4) + SUBSTRING(@yesterday, 3, 2) + LEFT(@yesterday, 2));
    DELETE FROM triovisttrade.ozon.fbo_ostatki WHERE load_date = @file_date;
    DELETE FROM triovisttrade.ozon.fbs_ostatki WHERE load_date = @file_date;

    -- ================= FBO ЗАГРУЗКА ================= --
    SET @file_path = @fbo_path + 'fbo_' + @today + '.csv';
    SET @file_date = TRY_CONVERT(DATE, 
                        RIGHT(@today, 4) + 
                        SUBSTRING(@today, 3, 2) + 
                        LEFT(@today, 2));

    
    BEGIN TRY
        IF OBJECT_ID('tempdb..#temp_fbo') IS NOT NULL DROP TABLE #temp_fbo;
        CREATE TABLE #temp_fbo (
            offer_id BIGINT,
            name NVARCHAR(255),
            fbo_balance INT,
            fbo_in_transit INT,
            fbo_in_request INT
        );
        
        SET @sql = N'
            BULK INSERT #temp_fbo
            FROM ''' + @file_path + '''
            WITH (
                FIRSTROW = 2,
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''0x0a'',
                CODEPAGE = ''65001'',
                ERRORFILE = ''' + @fbo_path + 'fbo_error.log''
            )';
        
        EXEC sp_executesql @sql;
        
        -- Вставка с проверкой NULL для обязательных полей
        INSERT INTO triovisttrade.mp.ozon_fbo_ostatki_21vek (
            load_date, 
            file_name, 
            offer_id, 
            name, 
            fbo_balance, 
            fbo_in_transit, 
            fbo_in_request
        )
        SELECT 
            ISNULL(@file_date, CAST(GETDATE() AS DATE)),
            ISNULL(@file_path, ''),
            ISNULL(offer_id, 0),
            NULLIF(name, ''),
            ISNULL(fbo_balance, 0),
            ISNULL(fbo_in_transit, 0),
            ISNULL(fbo_in_request, 0)
        FROM #temp_fbo
        WHERE offer_id IS NOT NULL; -- Пропускаем записи без offer_id
        
        PRINT 'FBO loaded: ' + @file_path;
    END TRY
    BEGIN CATCH
        PRINT 'FBO error: ' + ERROR_MESSAGE();
    END CATCH

    -- ================= FBS ЗАГРУЗКА ================= --
    SET @file_path = @fbs_path + 'fbs_' + @today + '.csv';
    SET @file_date = TRY_CONVERT(DATE, 
                        RIGHT(@today, 4) + 
                        SUBSTRING(@today, 3, 2) + 
                        LEFT(@today, 2));
    
    BEGIN TRY
        IF OBJECT_ID('tempdb..#temp_fbs') IS NOT NULL DROP TABLE #temp_fbs;
        CREATE TABLE #temp_fbs (
            offer_id BIGINT,
            name NVARCHAR(255),
            fbs_balance INT,
            sku_float FLOAT
        );
        
        SET @sql = N'
            BULK INSERT #temp_fbs
            FROM ''' + @file_path + '''
            WITH (
                FIRSTROW = 2,
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''0x0a'',
                CODEPAGE = ''65001'',
                ERRORFILE = ''' + @fbs_path + 'fbs_error.log''
            )';
        
        EXEC sp_executesql @sql;
        
        -- Вставка с проверкой NULL для всех полей, особенно sku
        INSERT INTO triovisttrade.mp.ozon_fbs_ostatki_21vek (
            load_date, 
            file_name, 
            offer_id, 
            name, 
            fbs_balance, 
            sku
        )
        SELECT 
            ISNULL(@file_date, CAST(GETDATE() AS DATE)),
            ISNULL(@file_path, ''),
            ISNULL(offer_id, 0),
            NULLIF(name, ''),
            ISNULL(fbs_balance, 0),
            CASE 
                WHEN sku_float IS NULL THEN 0
                WHEN sku_float = 0 THEN 0
                ELSE CAST(sku_float AS BIGINT)
            END
        FROM #temp_fbs
        WHERE offer_id IS NOT NULL -- Пропускаем записи без offer_id
        AND sku_float IS NOT NULL; -- И без sku
        
        PRINT 'FBS loaded: ' + @file_path;
    END TRY
    BEGIN CATCH
        PRINT 'FBS error: ' + ERROR_MESSAGE();
    END CATCH
END;

давай наплодим много таблиц и сделаем тупым способом как тут. но чтоб оно работало:
USE [triovisttrade]
GO
/****** Object:  StoredProcedure [mp].[OstatkiData_21vek_pbi]    Script Date: 30.06.2025 20:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [mp].[OstatkiData_21vek_pbi]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE 
        @fbo_path NVARCHAR(500) = N'D:\power bi\Python data\ozon_data\ostatki_21vek\fbo\',
        @fbs_path NVARCHAR(500) = N'D:\power bi\Python data\ozon_data\ostatki_21vek\fbs\',
        @today CHAR(8) = FORMAT(GETDATE(), 'ddMMyyyy'),
        @yesterday CHAR(8) = FORMAT(DATEADD(DAY, -1, GETDATE()), 'ddMMyyyy'),
        @sql NVARCHAR(MAX),
        @file_path NVARCHAR(500),
        @file_date DATE;

	SET @file_date = TRY_CONVERT(DATE, RIGHT(@today, 4) + SUBSTRING(@today, 3, 2) + LEFT(@today, 2));
    DELETE FROM triovisttrade.ozon.fbo_ostatki WHERE load_date = @file_date;
    DELETE FROM triovisttrade.ozon.fbs_ostatki WHERE load_date = @file_date;

    SET @file_date = TRY_CONVERT(DATE, RIGHT(@yesterday, 4) + SUBSTRING(@yesterday, 3, 2) + LEFT(@yesterday, 2));
    DELETE FROM triovisttrade.ozon.fbo_ostatki WHERE load_date = @file_date;
    DELETE FROM triovisttrade.ozon.fbs_ostatki WHERE load_date = @file_date;

    -- ================= FBO ЗАГРУЗКА ================= --
    SET @file_path = @fbo_path + 'fbo_' + @today + '.csv';
    SET @file_date = TRY_CONVERT(DATE, 
                        RIGHT(@today, 4) + 
                        SUBSTRING(@today, 3, 2) + 
                        LEFT(@today, 2));

    
    BEGIN TRY
        IF OBJECT_ID('tempdb..#temp_fbo') IS NOT NULL DROP TABLE #temp_fbo;
        CREATE TABLE #temp_fbo (
            offer_id BIGINT,
            name NVARCHAR(255),
            fbo_balance INT,
            fbo_in_transit INT,
            fbo_in_request INT
        );
        
        SET @sql = N'
            BULK INSERT #temp_fbo
            FROM ''' + @file_path + '''
            WITH (
                FIRSTROW = 2,
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''0x0a'',
                CODEPAGE = ''65001'',
                ERRORFILE = ''' + @fbo_path + 'fbo_error.log''
            )';
        
        EXEC sp_executesql @sql;
        
        -- Вставка с проверкой NULL для обязательных полей
        INSERT INTO triovisttrade.mp.ozon_fbo_ostatki_21vek (
            load_date, 
            file_name, 
            offer_id, 
            name, 
            fbo_balance, 
            fbo_in_transit, 
            fbo_in_request
        )
        SELECT 
            ISNULL(@file_date, CAST(GETDATE() AS DATE)),
            ISNULL(@file_path, ''),
            ISNULL(offer_id, 0),
            NULLIF(name, ''),
            ISNULL(fbo_balance, 0),
            ISNULL(fbo_in_transit, 0),
            ISNULL(fbo_in_request, 0)
        FROM #temp_fbo
        WHERE offer_id IS NOT NULL; -- Пропускаем записи без offer_id
        
        PRINT 'FBO loaded: ' + @file_path;
    END TRY
    BEGIN CATCH
        PRINT 'FBO error: ' + ERROR_MESSAGE();
    END CATCH

    -- ================= FBS ЗАГРУЗКА ================= --
    SET @file_path = @fbs_path + 'fbs_' + @today + '.csv';
    SET @file_date = TRY_CONVERT(DATE, 
                        RIGHT(@today, 4) + 
                        SUBSTRING(@today, 3, 2) + 
                        LEFT(@today, 2));
    
    BEGIN TRY
        IF OBJECT_ID('tempdb..#temp_fbs') IS NOT NULL DROP TABLE #temp_fbs;
        CREATE TABLE #temp_fbs (
            offer_id BIGINT,
            name NVARCHAR(255),
            fbs_balance INT,
            sku_float FLOAT
        );
        
        SET @sql = N'
            BULK INSERT #temp_fbs
            FROM ''' + @file_path + '''
            WITH (
                FIRSTROW = 2,
                FIELDTERMINATOR = '','',
                ROWTERMINATOR = ''0x0a'',
                CODEPAGE = ''65001'',
                ERRORFILE = ''' + @fbs_path + 'fbs_error.log''
            )';
        
        EXEC sp_executesql @sql;
        
        -- Вставка с проверкой NULL для всех полей, особенно sku
        INSERT INTO triovisttrade.mp.ozon_fbs_ostatki_21vek (
            load_date, 
            file_name, 
            offer_id, 
            name, 
            fbs_balance, 
            sku
        )
        SELECT 
            ISNULL(@file_date, CAST(GETDATE() AS DATE)),
            ISNULL(@file_path, ''),
            ISNULL(offer_id, 0),
            NULLIF(name, ''),
            ISNULL(fbs_balance, 0),
            CASE 
                WHEN sku_float IS NULL THEN 0
                WHEN sku_float = 0 THEN 0
                ELSE CAST(sku_float AS BIGINT)
            END
        FROM #temp_fbs
        WHERE offer_id IS NOT NULL -- Пропускаем записи без offer_id
        AND sku_float IS NOT NULL; -- И без sku
        
        PRINT 'FBS loaded: ' + @file_path;
    END TRY
    BEGIN CATCH
        PRINT 'FBS error: ' + ERROR_MESSAGE();
    END CATCH
END;