chandu1551
3/23/2016 - 7:01 PM

Update List of values with new list

Update List of values with new list

------------1. Build Latest Source Account Skills(CPS)  
			DECLARE @tblCenterPguSkill TABLE (CenterPguSkillId INT);

			INSERT INTO @tblCenterPguSkill (CenterPguSkillId)
			SELECT s.[Value]
			FROM dbo.Spliter(@csvCenterPguSkillIds, ',') s
			WHERE s.[Value] > '';

			------------2. Inactivate all records which are not in latest source  
			UPDATE acps
			SET acps.IsActive = 0
				,acps.ModifiedBy = @createdBy
				,acps.ModifiedDate = GETDATE()
			FROM dbo.AccountXCenterPguSkill acps
			LEFT JOIN @tblCenterPguSkill tcps ON tcps.CenterPguSkillId = acps.CenterPguSkillId
			WHERE acps.AccountId = @accountId
				AND tcps.CenterPguSkillId IS NULL

			------------3. Activate all records which are in latest source  
			UPDATE acps
			SET acps.IsActive = 1
				,acps.ModifiedBy = @createdBy
				,acps.ModifiedDate = GETDATE()
			FROM dbo.AccountXCenterPguSkill acps
			INNER JOIN @tblCenterPguSkill tcps ON acps.CenterPguSkillId = tcps.CenterPguSkillId
			WHERE acps.AccountId = @accountId

			------------4. Insert records which are not in table but in latest source  
			INSERT INTO dbo.AccountXCenterPguSkill (
				AccountId
				,CenterPguSkillId
				,IsActive
				,CreatedBy
				,CreatedDate
				)
			SELECT @accountId
				,tcps.CenterPguSkillId
				,1
				,@createdBy
				,GETDATE()
			FROM @tblCenterPguSkill tcps
			LEFT JOIN dbo.AccountXCenterPguSkill acps ON acps.AccountId = @accountId
				AND acps.CenterPguSkillId = tcps.CenterPguSkillId
			WHERE acps.CenterPguSkillId IS NULL