-- 使用W3数据库
USE [W3]; -- 切换到W3数据库
GO
------ 1. 自动查找并导入Excel文件 ------
DECLARE @BasePath NVARCHAR(500) = 'C:\Users\ZhuanZ(无密码)\Desktop\品质月报\'; -- 定义基础路径
DECLARE @YearFolder NVARCHAR(10) = FORMAT(GETDATE(), 'yyyy年'); -- 获取当前年份并格式化为“yyyy年”
DECLARE @MonthFolder NVARCHAR(10) = FORMAT(GETDATE(), 'M月'); -- 获取当前月份并格式化为“M月”
DECLARE @SubFolderKeyword NVARCHAR(50) = '3#数据'; -- 定义子文件夹关键词
DECLARE @FileKeyword NVARCHAR(50) = '3#完成车日报'; -- 定义文件关键词
PRINT '=== 开始执行 ==='; -- 打印开始标志
PRINT '基础路径: ' + @BasePath; -- 打印基础路径
PRINT '年份文件夹: ' + @YearFolder; -- 打印年份文件夹
PRINT '月份文件夹: ' + @MonthFolder; -- 打印月份文件夹
PRINT '子文件夹关键词: ' + @SubFolderKeyword; -- 打印子文件夹关键词
PRINT '文件关键词: ' + @FileKeyword; -- 打印文件关键词
DECLARE @SubFolderName NVARCHAR(255) = NULL; -- 用于存放找到的子文件夹名
DECLARE @FileName NVARCHAR(255) = NULL; -- 用于存放找到的文件名
DECLARE @FullPath NVARCHAR(1000) = NULL; -- 用于存放完整文件路径
DECLARE @Cmd NVARCHAR(500); -- 用于存放命令字符串
IF OBJECT_ID('tempdb..#FolderList') IS NOT NULL DROP TABLE #FolderList; -- 如果临时表#FolderList存在则删除
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList; -- 如果临时表#FileList存在则删除
BEGIN TRY -- 开始异常捕获
EXEC sp_configure 'show advanced options', 1; -- 启用高级选项
RECONFIGURE; -- 重新配置
EXEC sp_configure 'xp_cmdshell', 1; -- 启用xp_cmdshell
RECONFIGURE; -- 重新配置
CREATE TABLE #FolderList (FolderName NVARCHAR(255)); -- 创建临时表存放文件夹名
CREATE TABLE #FileList (FileName NVARCHAR(255)); -- 创建临时表存放文件名
SET @Cmd = 'dir /b /ad "' + @BasePath + @YearFolder + '\' + @MonthFolder + '\*"'; -- 构建查找子文件夹的命令
PRINT '执行命令: ' + @Cmd; -- 打印命令
INSERT INTO #FolderList EXEC xp_cmdshell @Cmd; -- 执行命令并将结果插入#FolderList
SELECT '文件夹搜索结果' AS Info, * FROM #FolderList; -- 显示文件夹搜索结果
SELECT TOP 1 @SubFolderName = FolderName -- 查找第一个包含关键词的子文件夹
FROM #FolderList
WHERE FolderName LIKE '%3%#%数%据%'
AND FolderName IS NOT NULL
AND FolderName <> 'NULL';
PRINT '找到的子文件夹: ' + ISNULL(@SubFolderName, 'NULL'); -- 打印找到的子文件夹名
IF @SubFolderName IS NOT NULL -- 如果找到了子文件夹
BEGIN
SET @Cmd = 'dir /b "' + @BasePath + @YearFolder + '\' + @MonthFolder + '\' + @SubFolderName + '\*.xls*"'; -- 构建查找Excel文件的命令
PRINT '执行命令: ' + @Cmd; -- 打印命令
INSERT INTO #FileList EXEC xp_cmdshell @Cmd; -- 执行命令并将结果插入#FileList
SELECT '文件搜索结果' AS Info, * FROM #FileList; -- 显示文件搜索结果
SELECT TOP 1 @FileName = FileName -- 查找第一个包含关键词的文件名
FROM #FileList
WHERE FileName LIKE '%3#%完成车日报%'
AND FileName IS NOT NULL
AND FileName <> 'NULL';
PRINT '找到的文件名: ' + ISNULL(@FileName, 'NULL'); -- 打印找到的文件名
IF @FileName IS NOT NULL -- 如果找到了文件
BEGIN
SET @FullPath = @BasePath + @YearFolder + '\' + @MonthFolder + '\' + @SubFolderName + '\' + @FileName; -- 拼接完整文件路径
PRINT '完整文件路径: ' + @FullPath; -- 打印完整文件路径
END
END
EXEC sp_configure 'xp_cmdshell', 0; -- 关闭xp_cmdshell
RECONFIGURE; -- 重新配置
EXEC sp_configure 'show advanced options', 0; -- 关闭高级选项
RECONFIGURE; -- 重新配置
END TRY
BEGIN CATCH -- 捕获异常
PRINT '配置错误: ' + ERROR_MESSAGE(); -- 打印错误信息
IF OBJECT_ID('tempdb..#FolderList') IS NOT NULL DROP TABLE #FolderList; -- 删除临时表
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList; -- 删除临时表
RETURN; -- 退出
END CATCH;
------ 2. 导入Excel到临时表 ------
IF @FullPath IS NOT NULL -- 如果找到了完整文件路径
BEGIN
IF OBJECT_ID('完成车不良临时表', 'U') IS NOT NULL
DROP TABLE 完成车不良临时表; -- 如果表已存在则先删除
BEGIN TRY
DECLARE @ImportSQL NVARCHAR(MAX) = N'
SELECT *
INTO 完成车不良临时表
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;IMEX=1;Database=' + REPLACE(@FullPath, '''', '''''') + ''',
''SELECT * FROM [完成车不良清单$]''
)'; -- 构建导入Excel的SQL语句
PRINT '执行导入SQL: ' + LEFT(@ImportSQL, 200) + '...'; -- 打印导入SQL
EXEC sp_executesql @ImportSQL; -- 执行导入
PRINT '导入成功，共导入 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 行数据'; -- 打印导入行数
END TRY
BEGIN CATCH
PRINT '导入失败: ' + ERROR_MESSAGE(); -- 打印导入失败信息
IF OBJECT_ID('tempdb..#FolderList') IS NOT NULL DROP TABLE #FolderList; -- 删除临时表
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList; -- 删除临时表
RETURN; -- 退出
END CATCH;
END
ELSE
BEGIN
PRINT '未找到匹配的文件，可能原因:'; -- 打印未找到文件的原因
PRINT '1. 文件夹结构不符合预期';
PRINT '2. 关键词匹配失败';
PRINT '3. 文件不存在或路径错误';
IF OBJECT_ID('tempdb..#FolderList') IS NOT NULL DROP TABLE #FolderList; -- 删除临时表
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList; -- 删除临时表
RETURN; -- 退出
END
------ 3. 只保留关键词列 ------
ALTER TABLE 完成车不良临时表 --删除F17这个列
DROP COLUMN F17;
CREATE TABLE ##ColumnsToKeep (ColumnName NVARCHAR(128)); -- 创建全局临时表存放要保留的列名
DECLARE @KeepKeywords TABLE (Keyword NVARCHAR(50)); -- 创建表变量存放关键词
INSERT INTO @KeepKeywords VALUES
('月份'),('日期'),('指摘属性'),('车号'),('车型'),('部位'),
('不良内容'),('不良属性'),('班次'),('发生源'),('流出源'),('备注'); -- 插入所有需要保留的关键词
DECLARE @CheckColumnsSQL NVARCHAR(MAX) = ''; -- 定义变量用于拼接SQL
DECLARE @ColumnName NVARCHAR(128); -- 定义变量存放列名
DECLARE @ColumnCursor CURSOR; -- 定义游标
SET @ColumnCursor = CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = '完成车不良临时表'; -- 查询所有列名
OPEN @ColumnCursor; -- 打开游标
FETCH NEXT FROM @ColumnCursor INTO @ColumnName; -- 读取第一列
WHILE @@FETCH_STATUS = 0 -- 遍历所有列
BEGIN
DECLARE @CheckSQL NVARCHAR(MAX) = N'
IF EXISTS (
SELECT 1 FROM [dbo].[完成车不良临时表]
WHERE [' + @ColumnName + '] IS NOT NULL
AND ('; -- 拼接判断列是否包含关键词的SQL
DECLARE @Condition NVARCHAR(MAX) = '';
SELECT @Condition = @Condition + CASE WHEN @Condition = '' THEN '' ELSE ' OR ' END +
'CAST([' + @ColumnName + '] AS NVARCHAR(MAX)) LIKE ''%' + Keyword + '%'''
FROM @KeepKeywords; -- 拼接所有关键词
SET @CheckSQL = @CheckSQL + @Condition + ')
)
BEGIN
INSERT INTO ##ColumnsToKeep VALUES (''' + @ColumnName + ''');
END';
EXEC sp_executesql @CheckSQL; -- 执行SQL，插入需要保留的列
FETCH NEXT FROM @ColumnCursor INTO @ColumnName; -- 读取下一列
END
CLOSE @ColumnCursor; -- 关闭游标
DEALLOCATE @ColumnCursor; -- 释放游标
DECLARE @DropColumnsSQL NVARCHAR(MAX) = ''; -- 定义变量用于拼接删除列的SQL
SELECT @DropColumnsSQL = @DropColumnsSQL +
'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME = ''完成车不良临时表''
AND COLUMN_NAME = ''' + COLUMN_NAME + ''') ' +
'BEGIN
ALTER TABLE [dbo].[完成车不良临时表] DROP COLUMN [' + COLUMN_NAME + '];
PRINT ''已删除列: ' + COLUMN_NAME + ''';
END;' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = '完成车不良临时表'
AND COLUMN_NAME NOT IN (
SELECT ColumnName FROM ##ColumnsToKeep
)
AND (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '完成车不良临时表') > 1; -- 拼接所有需要删除的列
IF LEN(@DropColumnsSQL) > 0
BEGIN
EXEC sp_executesql @DropColumnsSQL; -- 执行删除列的SQL
PRINT '已删除所有不包含关键词的列'; -- 打印删除结果
SELECT '保留的列' AS Info, ColumnName FROM ##ColumnsToKeep; -- 显示保留的列
END
ELSE
BEGIN
PRINT '没有需要删除的列（所有列都包含关键词）'; -- 打印无需删除
END
IF OBJECT_ID('tempdb..##ColumnsToKeep') IS NOT NULL
DROP TABLE ##ColumnsToKeep; -- 删除全局临时表
GO
------ 4. 删除所有列都为空值的行 ------
DECLARE @CheckColumnsSQL2 NVARCHAR(MAX) = ''; -- 定义变量拼接所有列都为NULL的条件
DECLARE @DeleteSQL NVARCHAR(MAX) = ''; -- 定义变量拼接删除SQL
DECLARE @DebugSQL NVARCHAR(MAX) = ''; -- 定义变量拼接调试SQL
SELECT @CheckColumnsSQL2 = @CheckColumnsSQL2 + CASE WHEN @CheckColumnsSQL2 = '' THEN '' ELSE ' AND ' END + '[' + COLUMN_NAME + '] IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = '完成车不良临时表'
ORDER BY ORDINAL_POSITION; -- 拼接所有列都为NULL的条件
IF LEN(@CheckColumnsSQL2) > 0
BEGIN
SET @DebugSQL = 'SELECT COUNT(*) AS 空行数 FROM [dbo].[完成车不良临时表] WHERE ' + @CheckColumnsSQL2; -- 拼接统计空行数的SQL
SET @DeleteSQL = '
DECLARE @BeforeCount INT = (SELECT COUNT(*) FROM [dbo].[完成车不良临时表]);
PRINT ''将要执行的删除条件: ' + REPLACE(@CheckColumnsSQL2, '''', '''''') + ''';
DELETE FROM [dbo].[完成车不良临时表] WHERE ' + @CheckColumnsSQL2 + ';
DECLARE @AfterCount INT = (SELECT COUNT(*) FROM [dbo].[完成车不良临时表]);
DECLARE @DeletedRows INT = @BeforeCount - @AfterCount;
PRINT ''删除前总行数: '' + CAST(@BeforeCount AS NVARCHAR(10));
PRINT ''删除后总行数: '' + CAST(@AfterCount AS NVARCHAR(10));
PRINT ''已删除所有列都为空值的行: '' + CAST(@DeletedRows AS NVARCHAR(10)) + '' 行'';
SELECT TOP 5 * FROM [dbo].[完成车不良临时表];'; -- 拼接删除空行的SQL
PRINT '将要检查的空行条件:'; -- 打印条件
PRINT @CheckColumnsSQL2;
PRINT '预计会删除的空行数:'; -- 打印预计删除行数
EXEC sp_executesql @DebugSQL; -- 执行统计空行数
EXEC sp_executesql @DeleteSQL; -- 执行删除空行
END
ELSE
BEGIN
PRINT '没有可用于检查的列，无法执行删除空行操作'; -- 打印无可用列
END
GO
------ 5. 删除表头及其上方所有行 ------
DECLARE @cols NVARCHAR(MAX) = ''; -- 定义变量拼接所有列的LIKE条件
SELECT @cols = @cols + ' OR [' + COLUMN_NAME + '] LIKE N''%发生源%'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '完成车不良临时表'; -- 拼接所有列的LIKE条件
SET @cols = STUFF(@cols, 1, 4, ''); -- 去掉第一个 OR
DECLARE @RowNum INT = NULL; -- 定义变量存放行号
DECLARE @sql NVARCHAR(MAX) = '
WITH AllRows AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn, *
FROM [dbo].[完成车不良临时表]
)
SELECT TOP 1 @RowNum_OUT = rn
FROM AllRows
WHERE ' + @cols; -- 拼接查找包含“日期”关键词行号的SQL
EXEC sp_executesql @sql, N'@RowNum_OUT INT OUTPUT', @RowNum_OUT=@RowNum OUTPUT; -- 执行SQL并输出行号
PRINT N'“发生源”关键词所在的行号为: ' + ISNULL(CAST(@RowNum AS NVARCHAR(10)), N'未找到'); -- 打印行号
IF @RowNum IS NOT NULL AND @RowNum >= 1
BEGIN
;WITH T AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn
FROM [dbo].[完成车不良临时表]
)
DELETE FROM T WHERE rn <= @RowNum; -- 删除该行及其上方所有行
PRINT N'已删除1~' + CAST(@RowNum AS NVARCHAR(10)) + N'行数据'; -- 打印删除结果
END
ELSE
BEGIN
PRINT N'未找到“发生源”关键词行，无需删除'; -- 打印未找到
END
GO
EXEC sp_help '完成车不良临时表'; -- 显示表结构
SELECT TOP 10 * FROM [dbo].[完成车不良临时表]; -- 显示前10行数据
GO
------ 6. 重命名部分列为中文含义 ------
DECLARE @RenameSQL NVARCHAR(MAX) = ''; -- 定义变量拼接重命名SQL
SELECT @RenameSQL = @RenameSQL +
'EXEC sp_rename ''[dbo].[完成车不良临时表].[' + COLUMN_NAME + ']'', ''' +
CASE COLUMN_NAME
WHEN 'F4' THEN '指摘属性'
WHEN 'F5' THEN '车号'
WHEN 'F6' THEN '车型'
WHEN 'F7' THEN '部位'
WHEN 'F8' THEN '不良内容'
WHEN 'F9' THEN '不良属性'
WHEN 'F10' THEN '班次'
WHEN 'F11' THEN '发生源'
WHEN 'F12' THEN '流出源'
WHEN 'F13' THEN '备注'
ELSE COLUMN_NAME
END + ''', ''COLUMN'';' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = '完成车不良临时表'
AND COLUMN_NAME IN ('F4','F5','F6','F7','F8','F9','F10','F11','F12','F13'); -- 拼接所有需要重命名的列
IF LEN(@RenameSQL) > 0
BEGIN
EXEC sp_executesql @RenameSQL; -- 执行重命名
PRINT '已重命名所有列'; -- 打印结果
END
ELSE
BEGIN
PRINT '未找到需要重命名的列'; -- 打印未找到
END
GO
SELECT TOP 5 * FROM [dbo].[完成车不良临时表]; -- 查询前5行数据
GO
EXEC sp_help '完成车不良临时表'; -- 显示表结构
GO
------ 7. 合并第一、第二列为“YYYY-MM-DD”，并添加“时间序列”列 ------
ALTER TABLE [dbo].[完成车不良临时表] ADD 日期 NVARCHAR(20), 时间序列 NVARCHAR(20); -- 新增“日期”和“时间序列”两列
GO
DECLARE @CurrentYear NVARCHAR(4) = CONVERT(NVARCHAR(4), YEAR(GETDATE())); -- 获取当前年份
DECLARE @CurrentMonth NVARCHAR(2) = RIGHT('0' + CONVERT(NVARCHAR(2), MONTH(GETDATE())), 2); -- 获取当前月份
;WITH T1 AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn -- 为每一行生成递增序号
FROM [dbo].[完成车不良临时表]
)
UPDATE T1
SET
日期 =
@CurrentYear + '-' +
RIGHT('0' +
REPLACE(REPLACE(CONVERT(NVARCHAR(10), T1.F2), '月', ''), ' ', '')
,2) + '-' +
RIGHT('0' +
REPLACE(REPLACE(CONVERT(NVARCHAR(10), T1.F3), '日', ''), ' ', '')
,2), -- 合并F2和F4列为“YYYY-MM-DD”格式
时间序列 =
@CurrentYear +
@CurrentMonth +
'01' +
RIGHT('000000' + CONVERT(NVARCHAR(6), rn), 6); -- 生成时间序列，格式为“YYYYMM01000001”递增
EXEC sp_help '完成车不良临时表'; -- 显示表结构
SELECT TOP 10 * FROM [dbo].[完成车不良临时表]; -- 显示前10行数据
GO
------ 8. 删除F2、F4列，并将“日期”列移到第一列 ------
ALTER TABLE [dbo].[完成车不良临时表] DROP COLUMN F2, F3; -- 删除F2、F4列
GO
SELECT
日期, -- 将“日期”列放到第一位
指摘属性,车号,车型,部位,
不良内容,不良属性,班次,发生源,流出源,备注, 时间序列-- 其余列顺序保持不变
INTO 完成车不良临时表_新
FROM [dbo].[完成车不良临时表]; -- 创建新表，调整列顺序
DROP TABLE [dbo].[完成车不良临时表]; -- 删除原表
EXEC sp_rename '完成车不良临时表_新', '完成车不良临时表'; -- 重命名新表为原表名
GO
EXEC sp_help '完成车不良临时表'; -- 显示最终表结构
SELECT TOP 10 * FROM [dbo].[完成车不良临时表]; -- 显示前10行数据
-------------------------------------------------------------------------------------------------------------------------------------------------
-- 设定批处理的初始数据库上下文
USE W3;
-- 声明变量，用于存储我们要记录到监控表的信息
-- 作业名称
DECLARE @JobName NVARCHAR(200) = N'完成车日报导入作业';
-- 插入行数
DECLARE @RowsInserted INT = 0;
-- 更新行数
DECLARE @RowsUpdated INT = 0;
-- 删除行数
DECLARE @RowsDeleted INT = 0;
-- 执行结果
DECLARE @ExecutionResult NVARCHAR(50) = NULL;
-- 错误信息
DECLARE @ErrorMessage NVARCHAR(MAX) = NULL;
-- 下次执行时间
DECLARE @NextExecutionTime DATETIME = NULL;
-- 声明动态SQL所需的变量
DECLARE @SQL NVARCHAR(MAX);
-- Excel路径
DECLARE @ExcelPath NVARCHAR(MAX) = N'C:\Users\ZhuanZ(无密码)\Desktop\品质月报\完成车日报.xlsx';
-- 工作表名称
DECLARE @SheetName NVARCHAR(MAX) = N'完成车不良清单$';
-- 1. 首先确定当前执行时间和下次执行时间
-- 获取当前时间
DECLARE @CurrentTime TIME = CAST(GETDATE() AS TIME);
-- 早晨执行时间
DECLARE @MorningRun TIME = '05:55:00';
-- 晚上执行时间
DECLARE @EveningRun TIME = '17:55:00';
-- 计算下次执行时间
IF @CurrentTime < @MorningRun
-- 如果当前时间早于早晨执行时间，下次执行时间为今天早晨
SET @NextExecutionTime = CAST(CAST(GETDATE() AS DATE) AS DATETIME) + CAST(@MorningRun AS DATETIME);
ELSE IF @CurrentTime BETWEEN @MorningRun AND @EveningRun
-- 如果当前时间在早晨和晚上执行时间之间，下次执行时间为今天晚上
SET @NextExecutionTime = CAST(CAST(GETDATE() AS DATE) AS DATETIME) + CAST(@EveningRun AS DATETIME);
ELSE
-- 否则下次执行时间为明天早晨
SET @NextExecutionTime = CAST(CAST(DATEADD(DAY, 1, GETDATE()) AS DATE) AS DATETIME) + CAST(@MorningRun AS DATETIME);
-- 开始尝试执行
BEGIN TRY
----------------------------------------------------------------------
-- 阶段2: 将临时表数据同步到最终目标表
----------------------------------------------------------------------
-- 2.1 更新目标表中已存在的记录 (只统计实际发生变化的行)
-- 更新目标表中已存在的记录
UPDATE t
SET
t.[日期] = e.[日期],
t.[指摘属性] = e.[指摘属性],
t.[车号] = e.[车号],
t.[车型] = e.[车型],
t.[部位] = e.[部位],
t.[不良内容] = e.[不良内容],
t.[不良属性] = e.[不良属性],
t.[班次] = e.[班次],
t.[发生源] = e.[发生源],
t.[流出源] = e.[流出源],
t.[备注] = e.[备注],
t.[时间序列] = e.[时间序列]
FROM [车体3科数据库].[dbo].[完成车不良] t
INNER JOIN [W3].[dbo].[完成车不良临时表] e ON t.[时间序列] = e.[时间序列]
-- 只更新有变化的记录
WHERE
ISNULL(t.[日期], '') <> ISNULL(e.[日期], '')
OR ISNULL(t.[指摘属性], '') <> ISNULL(e.[指摘属性], '')
OR ISNULL(t.[车号], '') <> ISNULL(e.[车号], '')
OR ISNULL(t.[车型], '') <> ISNULL(e.[车型], '')
OR ISNULL(t.[部位], '') <> ISNULL(e.[部位], '')
OR ISNULL(t.[不良内容], '') <> ISNULL(e.[不良内容], '')
OR ISNULL(t.[不良属性], '') <> ISNULL(e.[不良属性], '')
OR ISNULL(t.[班次], '') <> ISNULL(e.[班次], '')
OR ISNULL(t.[发生源], '') <> ISNULL(e.[发生源], '')
OR ISNULL(t.[流出源], '') <> ISNULL(e.[流出源], '')
OR ISNULL(t.[备注], '') <> ISNULL(e.[备注], '');
-- 记录更新行数
SET @RowsUpdated = @@ROWCOUNT;
-- 2.2 插入Excel中存在但目标表中不存在的记录
-- 插入新记录
INSERT INTO [车体3科数据库].[dbo].[完成车不良]
SELECT
e.[日期],e.[指摘属性],e.[车号],e.[车型],e.[部位],
e.[不良内容],e.[不良属性],e.[班次],e.[发生源],e.[流出源],
e.[备注],e.[时间序列]
FROM [W3].[dbo].[完成车不良临时表] e
-- 只插入目标表中不存在的记录
WHERE NOT EXISTS (
SELECT 1 FROM [车体3科数据库].[dbo].[完成车不良] t
WHERE t.[时间序列] = e.[时间序列]
);
-- 记录插入行数
SET @RowsInserted = @@ROWCOUNT;
-- 2.3 删除目标表中当月不存在于临时表中的记录
-- 开始事务
BEGIN TRANSACTION;
-- 删除目标表中当月不存在于临时表中的记录
DELETE FROM [车体3科数据库].[dbo].[完成车不良]
WHERE NOT EXISTS (
SELECT 1 FROM [W3].[dbo].[完成车不良临时表] e
WHERE [车体3科数据库].[dbo].[完成车不良].[时间序列] = e.[时间序列]
)
-- 只删除当月数据
AND [日期] >= DATEADD(day, 1-DAY(GETDATE()), CAST(GETDATE() AS DATE))
AND [日期] < DATEADD(month, 1, DATEADD(day, 1-DAY(GETDATE()), CAST(GETDATE() AS DATE)));
-- 记录删除行数
SET @RowsDeleted = @@ROWCOUNT;
-- 提交事务
COMMIT TRANSACTION;
-- 2.4 删除目标表中所有列都为空的记录
-- 删除所有列为空的记录
DELETE FROM [车体3科数据库].[dbo].[完成车不良]
WHERE
[日期] IS NULL
AND ([指摘属性] IS NULL OR LTRIM(RTRIM([指摘属性])) = '')
AND ([车号] IS NULL OR LTRIM(RTRIM([车号])) = '')
AND ([车型] IS NULL OR LTRIM(RTRIM([车型])) = '')
AND ([部位] IS NULL OR LTRIM(RTRIM([部位])) = '')
AND ([不良内容] IS NULL OR LTRIM(RTRIM([不良内容])) = '')
AND ([不良属性] IS NULL OR LTRIM(RTRIM([不良属性])) = '')
AND ([班次] IS NULL OR LTRIM(RTRIM([班次])) = '')
AND ([发生源] IS NULL OR LTRIM(RTRIM([发生源])) = '')
AND ([流出源] IS NULL OR LTRIM(RTRIM([流出源])) = '')
AND ([备注] IS NULL OR LTRIM(RTRIM([备注])) = '')
AND [时间序列] IS NULL;
-- 设置执行结果为成功
SET @ExecutionResult = N'成功';
END TRY
-- 捕获异常
BEGIN CATCH
-- 设置执行结果为失败
SET @ExecutionResult = N'失败';
-- 记录错误信息
SET @ErrorMessage = ERROR_MESSAGE();
-- 如果有未提交的事务则回滚
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH;
-- 无论成功或失败，都将执行结果记录到监控表
INSERT INTO [日志].[dbo].[SSIS包执行监控] (
[包名称],[执行时间],[新增数据量],[更新数据量],[删除数据量],[执行结果],[错误信息],[下次执行时间]
)
VALUES (
@JobName,
GETDATE(),
@RowsInserted,
@RowsUpdated,
@RowsDeleted,
@ExecutionResult,
@ErrorMessage,
@NextExecutionTime
);
-- 输出执行结果
PRINT '作业执行完成';
PRINT '执行结果: ' + ISNULL(@ExecutionResult, 'NULL');
PRINT '新增记录数: ' + CAST(@RowsInserted AS NVARCHAR(10));
PRINT '更新记录数: ' + CAST(@RowsUpdated AS NVARCHAR(10));
PRINT '删除记录数: ' + CAST(@RowsDeleted AS NVARCHAR(10));
PRINT '下次执行时间: ' + ISNULL(CONVERT(NVARCHAR(30), @NextExecutionTime, 120), 'NULL');
-- 如果有错误信息则打印
IF @ErrorMessage IS NOT NULL
PRINT '错误信息: ' + @ErrorMessage; 优化这个代码，然后发送你优化后的完整代码给我
最新发布
本文介绍如何使用 SSIS 中的 FileSystemTask 来删除旧文件并插入新文件。通过使用空模板覆盖原有文件的方法实现数据更新。
扫一扫
8978
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
