July 24, 2015 at 8:58 am
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
SELECT empno,
Max(CASE
WHEN = 'S' THEN [hrs]
END),
[effective date],
Max(CASE
WHEN = 'T' THEN [hrs]
END),
import_date
FROM tempaccrual T
GROUP BY empno,
[effective date],
[import_date]
July 24, 2015 at 9:12 am
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
SELECT empno,
AccruedVacation,
[effective date],
AccruedSick,
import_date
FROM (SELECT empno,
MAX(CASE
WHEN code = 'S' THEN hrs
END) AS AccruedVacation,
[effective date] AS accrued_sick_effective_date,
MAX(CASE
WHEN code = 'T' THEN hrs
END) AS AccruedSick,
import_date
FROM tempaccrual T
GROUP BY empno,
[effective date],
import_date
) s
WHERE NOT EXISTS ( SELECT 1
FROM vacationaccrual v
WHERE v.empno = s.empno AND v.accrued_vacation = s.accrued_vacation AND v.accrued_sick_effective_date = s.accrued_sick_effective_date AND v.accrued_sick = s.accrued_sick AND v.import_date = s.import_date )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2015 at 9:15 am
shezi (7/24/2015)
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
SELECT empno,
Max(CASE
WHEN
= 'S' THEN [hrs]END),
[effective date],
Max(CASE
WHEN
= 'T' THEN [hrs]END),
import_date
FROM tempaccrual T
GROUP BY empno,
[effective date],
[import_date]
You need to do a LEFT JOIN back to the destination table, and then check for a non-nullable column being null. Like this:
WITH T AS
(
SELECT empno,
Max(CASE
WHEN = 'S' THEN [hrs]
END) AS accrued_vacation,
[effective date],
Max(CASE
WHEN = 'T' THEN [hrs]
END) AS accrued_sick,
import_date
FROM tempaccrual T
GROUP BY empno,
[effective date],
[import_date]
)
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
SELECT T.empno,
T.accrued_vacation,
T.effective_date,
T.accrued_sick,
T.import_date
FROM T
LEFT JOIN vacationaccrual T2
ON T.empno = T2.empno
AND T.accrued_vacation = T2.accrued_vacation
AND T.effective_date = T2.accrued_sick_effective_date
AND T.accrued_sick = T2.accrued_sick
AND T.import_date = T2.import_date
WHERE T2.empno IS NULL;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 27, 2015 at 9:15 am
GilaMonster (7/24/2015)
INSERT INTO vacationaccrual(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
SELECT empno,
AccruedVacation,
[effective date],
AccruedSick,
import_date
FROM (SELECT empno,
MAX(CASE
WHEN code = 'S' THEN hrs
END) AS AccruedVacation,
[effective date] AS accrued_sick_effective_date,
MAX(CASE
WHEN code = 'T' THEN hrs
END) AS AccruedSick,
import_date
FROM tempaccrual T
GROUP BY empno,
[effective date],
import_date
) s
WHERE NOT EXISTS ( SELECT 1
FROM vacationaccrual v
WHERE v.empno = s.empno AND v.accrued_vacation = s.accrued_vacation AND v.accrued_sick_effective_date = s.accrued_sick_effective_date AND v.accrued_sick = s.accrued_sick AND v.import_date = s.import_date )
This works great but when there is null value in any of the fields then the checking fails and keep inserting duplicate rows. Please advise
July 27, 2015 at 10:56 am
shezi (7/27/2015)
This works great but when there is null value in any of the fields then the checking fails and keep inserting duplicate rows. Please advise
Did you try the solution that I posted? How does that work?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply