USE [triovisttrade] GO /****** Object: StoredProcedure [mp].[OstatkiData21vekpbi] Script…
- User
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 CATCHEND;
давай наплодим много таблиц и сделаем тупым способом как тут. но чтоб оно работало: 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 CATCHEND;
Assistant
Хорошо, давай сделаем "тупым способом", как ты просил — то есть будем дублировать код и создавать отдельные таблицы и процедуры для 21vek и sundays, но всё в одном месте.
Вот готовое решение:
✅ 1. Создание таблиц
-- Таблицы для 21vek (FBO и FBS) CREATE TABLE [mp].[ozon_fbo_ostatki_21vek] ( id BIGINT IDENTITY(1,1) PRIMARY KEY, load_date DATE NOT NULL, file_name NVARCHAR(500), offer_id BIGINT NOT NULL, name NVARCHAR(255), fbo_balance INT NOT NULL DEFAULT 0, fbo_in_transit INT NOT NULL DEFAULT 0, fbo_in_request INT NOT NULL DEFAULT 0 ); CREATE TABLE [mp].[ozon_fbs_ostatki_21vek] ( id BIGINT IDENTITY(1,1) PRIMARY KEY, load_date DATE NOT NULL, file_name NVARCHAR(500), offer_id BIGINT NOT NULL, name NVARCHAR(255), fbs_balance INT NOT NULL DEFAULT 0, sku BIGINT NOT NULL ); -- Таблицы для sundays (FBO и FBS) CREATE TABLE [mp].[ozon_fbo_ostatki_sundays] ( id BIGINT IDENTITY(1,1) PRIMARY KEY, load_date DATE NOT NULL, file_name NVARCHAR(500), offer_id BIGINT NOT NULL, name NVARCHAR(255), fbo_balance INT NOT NULL DEFAULT 0, fbo_in_transit INT NOT NULL DEFAULT 0, fbo_in_request INT NOT NULL DEFAULT 0 ); CREATE TABLE [mp].[ozon_fbs_ostatki_sundays] ( id BIGINT IDENTITY(1,1) PRIMARY KEY, load_date DATE NOT NULL, file_name NVARCHAR(500), offer_id BIGINT NOT NULL, name NVARCHAR(255), fbs_balance INT NOT NULL DEFAULT 0, sku BIGINT NOT NULL );
✅ 2. Обновленная процедура:
[mp].[OstatkiData_Ozon_combined_pbi]USE [triovisttrade] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Процедура: [mp].[OstatkiData_Ozon_combined_pbi] -- Назначение: Загрузка данных по остаткам Ozon из папок 21vek и sundays -- ============================================= ALTER PROCEDURE [mp].[OstatkiData_Ozon_combined_pbi] AS BEGIN SET NOCOUNT ON; -- Пути к файлам DECLARE @fbo_path_21vek NVARCHAR(500) = N'D:\power bi\Python data\marketplaces\ozon\ostatki_21vek\fbo\', @fbs_path_21vek NVARCHAR(500) = N'D:\power bi\Python data\marketplaces\ozon\ostatki_21vek\fbs\', @fbo_path_sundays NVARCHAR(500) = N'D:\power bi\Python data\marketplaces\ozon\ostatki_sundays\fbo\', @fbs_path_sundays NVARCHAR(500) = N'D:\power bi\Python data\marketplaces\ozon\ostatki_sundays\fbs\', @today CHAR(8) = FORMAT(GETDATE(), 'ddMMyyyy'), @yesterday CHAR(8) = FORMAT(DATEADD(DAY, -1, GETDATE()), 'ddMMyyyy'), @file_date DATE, @sql NVARCHAR(MAX); -- Удаляем данные за сегодня и вчера для всех таблиц SET @file_date = TRY_CONVERT(DATE, RIGHT(@today, 4) + SUBSTRING(@today, 3, 2) + LEFT(@today, 2)); DELETE FROM [mp].[ozon_fbo_ostatki_21vek] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbs_ostatki_21vek] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbo_ostatki_sundays] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbs_ostatki_sundays] WHERE load_date = @file_date; SET @file_date = TRY_CONVERT(DATE, RIGHT(@yesterday, 4) + SUBSTRING(@yesterday, 3, 2) + LEFT(@yesterday, 2)); DELETE FROM [mp].[ozon_fbo_ostatki_21vek] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbs_ostatki_21vek] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbo_ostatki_sundays] WHERE load_date = @file_date; DELETE FROM [mp].[ozon_fbs_ostatki_sundays] WHERE load_date = @file_date; -- Возвращаемся к текущей дате SET @file_date = TRY_CONVERT(DATE, RIGHT(@today, 4) + SUBSTRING(@today, 3, 2) + LEFT(@today, 2)); -- =================== ЗАГРУЗКА 21VEK =================== -- -- --- FBO --- DECLARE @file_path NVARCHAR(500) = @fbo_path_21vek + 'fbo_' + @today + '.csv'; BEGIN TRY IF OBJECT_ID('tempdb..#tmp_fbo_21vek') IS NOT NULL DROP TABLE #tmp_fbo_21vek; CREATE TABLE #tmp_fbo_21vek ( offer_id BIGINT, name NVARCHAR(255), fbo_balance INT, fbo_in_transit INT, fbo_in_request INT ); SET @sql = N' BULK INSERT #tmp_fbo_21vek FROM ''' + @file_path + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', CODEPAGE = ''65001'' )'; EXEC sp_executesql @sql; INSERT INTO [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 #tmp_fbo_21vek WHERE offer_id IS NOT NULL; PRINT 'FBO 21vek loaded: ' + @file_path; END TRY BEGIN CATCH PRINT 'FBO 21vek error: ' + ERROR_MESSAGE(); END CATCH -- --- FBS --- SET @file_path = @fbs_path_21vek + 'fbs_' + @today + '.csv'; BEGIN TRY IF OBJECT_ID('tempdb..#tmp_fbs_21vek') IS NOT NULL DROP TABLE #tmp_fbs_21vek; CREATE TABLE #tmp_fbs_21vek ( offer_id BIGINT, name NVARCHAR(255), fbs_balance INT, sku_float FLOAT ); SET @sql = N' BULK INSERT #tmp_fbs_21vek FROM ''' + @file_path + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', CODEPAGE = ''65001'' )'; EXEC sp_executesql @sql; INSERT INTO [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 #tmp_fbs_21vek WHERE offer_id IS NOT NULL AND sku_float IS NOT NULL; PRINT 'FBS 21vek loaded: ' + @file_path; END TRY BEGIN CATCH PRINT 'FBS 21vek error: ' + ERROR_MESSAGE(); END CATCH -- =================== ЗАГРУЗКА SUNDAYS =================== -- -- --- FBO --- SET @file_path = @fbo_path_sundays + 'fbo_' + @today + '.csv'; BEGIN TRY IF OBJECT_ID('tempdb..#tmp_fbo_sundays') IS NOT NULL DROP TABLE #tmp_fbo_sundays; CREATE TABLE #tmp_fbo_sundays ( offer_id BIGINT, name NVARCHAR(255), fbo_balance INT, fbo_in_transit INT, fbo_in_request INT ); SET @sql = N' BULK INSERT #tmp_fbo_sundays FROM ''' + @file_path + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', CODEPAGE = ''65001'' )'; EXEC sp_executesql @sql; INSERT INTO [mp].[ozon_fbo_ostatki_sundays] ( 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 #tmp_fbo_sundays WHERE offer_id IS NOT NULL; PRINT 'FBO sundays loaded: ' + @file_path; END TRY BEGIN CATCH PRINT 'FBO sundays error: ' + ERROR_MESSAGE(); END CATCH -- --- FBS --- SET @file_path = @fbs_path_sundays + 'fbs_' + @today + '.csv'; BEGIN TRY IF OBJECT_ID('tempdb..#tmp_fbs_sundays') IS NOT NULL DROP TABLE #tmp_fbs_sundays; CREATE TABLE #tmp_fbs_sundays ( offer_id BIGINT, name NVARCHAR(255), fbs_balance INT, sku_float FLOAT ); SET @sql = N' BULK INSERT #tmp_fbs_sundays FROM ''' + @file_path + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', CODEPAGE = ''65001'' )'; EXEC sp_executesql @sql; INSERT INTO [mp].[ozon_fbs_ostatki_sundays] ( 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 #tmp_fbs_sundays WHERE offer_id IS NOT NULL AND sku_float IS NOT NULL; PRINT 'FBS sundays loaded: ' + @file_path; END TRY BEGIN CATCH PRINT 'FBS sundays error: ' + ERROR_MESSAGE(); END CATCH END; GO
✅ 3. Пример вызова процедуры
EXEC [mp].[OstatkiData_Ozon_combined_pbi];
✅ 4. Пример выборки
-- Посмотреть последние загруженные данные SELECT TOP 10 * FROM [mp].[ozon_fbo_ostatki_21vek] ORDER BY load_date DESC; SELECT TOP 10 * FROM [mp].[ozon_fbs_ostatki_21vek] ORDER BY load_date DESC; SELECT TOP 10 * FROM [mp].[ozon_fbo_ostatki_sundays] ORDER BY load_date DESC; SELECT TOP 10 * FROM [mp].[ozon_fbs_ostatki_sundays] ORDER BY load_date DESC;
Если нужно — могу добавить логику обработки ошибок или сохранения незагруженных строк в отдельные файлы.
Также можно сделать отдельный лог с информацией о количестве записей и времени выполнения.Пишите, если хотите версию с автоматическим поиском файла по дате (если имя может отличаться).
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.