SELECT k.[Index] AS KontraktID, b.BrugerLogonNavn, b.BrugerNavn, b.BrugerTelefon, b.BrugerEmail, b.BrugerBeskrivelse
INTO #Brugere
FROM AD_Brugere AS b
INNER JOIN AD_OUs ON b.AD_OU_ID = AD_OUs.AD_OU_ID
LEFT OUTER JOIN NS_Kontrakter AS k ON AD_OUs.AD_OU_ID = k.AD_OU_ID
 
INSERT INTO AD_HistoriskeBrugere (KontraktID, BrugerLogonNavn, BrugerNavn, BrugerTelefon, BrugerEmail, BrugerBeskrivelse)
SELECT b.KontraktID, b.BrugerLogonNavn, b.BrugerNavn, b.BrugerTelefon, b.BrugerEmail, b.BrugerBeskrivelse
FROM #Brugere b
WHERE NOT EXISTS        (SELECT BrugerLogonNavn
                        FROM AD_HistoriskeBrugere hb
                        WHERE b.BrugerLogonNavn = hb.BrugerLogonNavn)
 
UPDATE AD_HistoriskeBrugere
SET KontraktID = b.KontraktID, BrugerLogonNavn = b.BrugerLogonNavn,
    BrugerNavn = b.BrugerNavn, BrugerTelefon = b.BrugerTelefon,
    BrugerEmail = b.BrugerEmail, BrugerBeskrivelse = b.BrugerBeskrivelse
FROM #Brugere b
WHERE EXISTS    (SELECT BrugerLogonNavn 
                FROM AD_HistoriskeBrugere hb
                WHERE b.BrugerLogonNavn = hb.BrugerLogonNavn)
 
DROP TABLE #Brugere

quote:

---

Originally posted by visakh16

you could just create a trigger on your Users table for delete,update to move the old values from users to your history table

---

CREATE TRIGGER LogHistoryInfo
ON User 
AFTER UPDATE,DELETE
AS

INSERT INTO User_History(reqdfields...)
SELECT reqdfields
FROM DELETED

quote:

---

Originally posted by visakh16

something like

CREATE TRIGGER LogHistoryInfo
ON User 
AFTER UPDATE,DELETE
AS

INSERT INTO User_History(reqdfields...)
SELECT reqdfields
FROM DELETED

---

quote:

---

Originally posted by thj

quote:

---

Originally posted by visakh16

something like

CREATE TRIGGER LogHistoryInfo
ON User 
AFTER UPDATE,DELETE
AS

INSERT INTO User_History(reqdfields...)
SELECT reqdfields
FROM DELETED

---

I'm not deleting or updating users in the Users table. It's beeing truncated and repopulated from Active Directory every night.

---

quote:

---

Originally posted by visakh16
then what you could do is to write a procedure which just copies all the records from users table to user history along with current datevalue and execute this procedure just before you truncate your table. you could include all this inside a sql job or just use DTS package with all this steps and execute it via job.

---

quote:

---

Originally posted by thj

quote:

---

Originally posted by visakh16
then what you could do is to write a procedure which just copies all the records from users table to user history along with current datevalue and execute this procedure just before you truncate your table. you could include all this inside a sql job or just use DTS package with all this steps and execute it via job.

---

Correct, and thats what I've been trying to do :-)
Insert non existing records and update existing. But the update part doesn't work.

---

quote:

---

Originally posted by visakh16
why is it not working. can you elaborate. may be with your update code?

---

quote:

---

Originally posted by thj

quote:

---

Originally posted by visakh16
why is it not working. can you elaborate. may be with your update code?

---

I already explained this in the first post:

"When I run this, it correctly inserts new users that does not exists in the UsersHistory table. The problem is the part that should Update the users that already exists in the UsersHistory table. Like I said, theres around 4300 users, and I stopped that UPDATE query after 30 minutes. Does it run in a loop or something? It can't take 30 minutes to update 4300 rows."

---

UPDATE AD_HistoriskeBrugere
SET KontraktID = b.KontraktID, BrugerLogonNavn = b.BrugerLogonNavn,
    BrugerNavn = b.BrugerNavn, BrugerTelefon = b.BrugerTelefon,
    BrugerEmail = b.BrugerEmail, BrugerBeskrivelse = b.BrugerBeskrivelse
FROM #Brugere b
INNER JOIN AD_HistoriskeBrugere hb
ON  b.BrugerLogonNavn = hb.BrugerLogonNavn

 

quote:

---

Originally posted by visakh16
have you tried using a join instead of exists?

UPDATE AD_HistoriskeBrugere
SET KontraktID = b.KontraktID, BrugerLogonNavn = b.BrugerLogonNavn,
    BrugerNavn = b.BrugerNavn, BrugerTelefon = b.BrugerTelefon,
    BrugerEmail = b.BrugerEmail, BrugerBeskrivelse = b.BrugerBeskrivelse
FROM #Brugere b
INNER JOIN AD_HistoriskeBrugere hb
ON  b.BrugerLogonNavn = hb.BrugerLogonNavn

---

quote:

---

Originally posted by thj

quote:

---

Originally posted by visakh16
have you tried using a join instead of exists?

UPDATE AD_HistoriskeBrugere
SET KontraktID = b.KontraktID, BrugerLogonNavn = b.BrugerLogonNavn,
    BrugerNavn = b.BrugerNavn, BrugerTelefon = b.BrugerTelefon,
    BrugerEmail = b.BrugerEmail, BrugerBeskrivelse = b.BrugerBeskrivelse
FROM #Brugere b
INNER JOIN AD_HistoriskeBrugere hb
ON  b.BrugerLogonNavn = hb.BrugerLogonNavn

---

Aah, so simple :-)
It's working. Thanks for your time man.

---