需要使用SSIS基于MAX条件将行数据从一个表更新到另一个表 [英] Need to update Row data from one table to other table based on MAX condition using SSIS

问题描述

我有2个表员工和部门 表格

I have 2 tables Employee and Department  tables

员工：

EmpID EmpName DOJ
1      Mohan      NULL
2      Manasa     NULL

部门：

DepID     DepName EmpName DOJ
1        Maths    Mohan K       2017-08-01
2        English  Mohan Kumar 2018-08-01
3        Science  Manasa K    2016-08-01
1        Social   Manasa       2017-09-01

我需要更新员工表使用部门表加入列的日期。


我需要根据MAX条件获得最新加入日期，并且需要使用SSIS使用LIKE条件。

Where i need to update Employee table Date of joining column using the Department table.
I need to get latest date of joining basing on the MAX Condition and need to use LIKE Condition using SSIS.

最初 我使用过EXECUTE SQL TASK并通过变量发送FULL RESULT SET并将该SQL TASK放入For Each循环容器中

Initially I have used EXECUTE SQL TASK and sending the FULL RESULT SET through variable and putting that SQL TASK into For Each loop container

并需要在Employee中更新如下表：

And need to update in the Employee table like below :

EmpID  EmpName   DOJ
1         Mohan   2018-08-01
2         Manasa  2017-09-01

我用过

EXECUTE SQL TASK：我在哪里写了查询'从EMPLOYEE中选择EMPNAME'并给出完整的结果集并存储在变量

EXECUTE SQL TASK : Where I have written query 'SELECT EMPNAME FROM EMPLOYEE' and given full result set and stored in a variable

FOR EACH LOOP CONTAINER：使用ADO.ENUMERATOR并使用该变量。

FOR EACH LOOP CONTAINER : used ADO.ENUMERATOR and used that variable .

建议我实现这个目标的方法

Suggest me the way to achieve this

推荐答案

嗨mohan1111，

Hi mohan1111,

您可以使用SSIS 执行SQL 任务。

You can use SSIS Execute SQL Task for that.

逻辑从 中检索第一个单词
部门 表格中的EmpName 列。

The logic retrieves the first word from the EmpName column in the Department table.

DECLARE @tbl_employee TABLE (
	EmpID INT NOT NULL
	, EmpName VARCHAR(100) NOT NULL
	, DOJ DATE NULL
);

INSERT INTO @tbl_employee
VALUES (1, 'Mohan', NULL)
	, (2, 'Manasa', NULL);

DECLARE @tbl_Department TABLE (
	DeptID INT NOT NULL
	, DepName VARCHAR(100) NOT NULL
	, EmpName VARCHAR(100) NOT NULL
	, DOJ DATE NOT NULL
);

INSERT INTO @tbl_Department
VALUES (1, 'Maths', 'Mohan K', '2017-08-01')
	, (2, 'English',  'Mohan Kumar', '2018-08-01')
	, (3, 'Science',  'Manasa K', '2016-08-01')
	, (2, 'Social',  'Manasa', '2017-09-01');

---- test only
---- get just the first word from the EmpName column
--SELECT LEFT(EmpName,(PATINDEX('% %',LTRIM(EmpName) + ' '))) AS EmpName, MAX(DOJ) AS DOJ
--FROM  @tbl_Department
--GROUP BY LEFT(EmpName,(PATINDEX('% %',LTRIM(EmpName) + ' ')))
--ORDER BY 1;

-- update @tbl_employee
MERGE INTO @tbl_employee AS T1
USING
(
	SELECT LEFT(EmpName,(PATINDEX('% %',LTRIM(EmpName) + ' '))) AS EmpName, MAX(DOJ) AS DOJ
	FROM  @tbl_Department
	GROUP BY LEFT(EmpName,(PATINDEX('% %',LTRIM(EmpName) + ' ')))
) AS T2
   ON T1.EmpName = T2.EmpName
WHEN MATCHED
THEN UPDATE
SET T1.DOJ = T2.DOJ;
-- see the result
SELECT * FROM @tbl_employee;

输出：

Output:

EmpID	EmpName	DOJ
1	Mohan	2018-08-01
2	Manasa	2017-09-01

这篇关于需要使用SSIS基于MAX条件将行数据从一个表更新到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！