Appending Records + Update

Hello, I have a need to refresh a table called Employees each day from Activ
e
Directory. This table will be used in a training database. When employees
terminate, they disappear from AD. So I would like to run a query that woul
d
select all of today's employees, and selectively append to the Employees
table. New employees would be added. If the employee no longer exists in
the AD data source, I'd like to set a value from 1 to 0 in the Active column
,
indicating that the employee is no longer active but keeping the record for
historical searches. Any suggestions would be appreciated. Thanks, Pancho.Hi
Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
compare Employees
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
> Hello, I have a need to refresh a table called Employees each day from
> Active
> Directory. This table will be used in a training database. When
> employees
> terminate, they disappear from AD. So I would like to run a query that
> would
> select all of today's employees, and selectively append to the Employees
> table. New employees would be added. If the employee no longer exists in
> the AD data source, I'd like to set a value from 1 to 0 in the Active
> column,
> indicating that the employee is no longer active but keeping the record
> for
> historical searches. Any suggestions would be appreciated. Thanks,
> Pancho.|||Sorry
Should be IF EXISTS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eM2JtevUGHA.5108@.tk2msftngp13.phx.gbl...
> Hi
> Take a look at IF EXEISTS , or WHERE NOT / EXISTS clauses in the BOL to
> compare Employees
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:1656A7F5-305D-420C-AE6F-3D6188C2A9FB@.microsoft.com...
>|||On Tue, 28 Mar 2006 08:50:01 -0800, Pancho wrote:
>Hello, I have a need to refresh a table called Employees each day from Acti
ve
>Directory. This table will be used in a training database. When employees
>terminate, they disappear from AD. So I would like to run a query that wou
ld
>select all of today's employees, and selectively append to the Employees
>table. New employees would be added. If the employee no longer exists in
>the AD data source, I'd like to set a value from 1 to 0 in the Active colum
n,
>indicating that the employee is no longer active but keeping the record for
>historical searches. Any suggestions would be appreciated. Thanks, Pancho.[/color
]
Hi Pancho,
UPDATE Employees
SET Active = 0
WHERE Active = 1
AND NOT EXISTS
(SELECT *
FROM AD
WHERE AD.KeyColumn = Employees.KeyColumn)
--
INSERT INTO Employees (KeyColumn, OtherColumn, Active)
SELECT KeyColumn, OtherColumn, 1
FROM AD
WHERE NOT EXISTS
(SELECT *
FROM Employees
WHERE AD.KeyColumn = Employees.KeyColumn)
Hugo Kornelis, SQL Server MVP