--CI_ACT_STATUS  A change in the action status
IF OBJECT_ID('TempDB..[#Temp1]','U') IS NOT NULL Drop table [#Temp1]
SELECT ACT_REF, TRANSACTION_TYPE, PRJVER_ID, HULL, WORKPACKAGE_ACTIVITY, WORKORDER_ACTIVITY, JOB_ACTIVITY, 
       ACT_TYPE, ACTIVITY_TYPE, WORKSTATION, OP, DEPT_ID, SCHEDULERELDATE, ACTUALRELDATE, ACT_PRESETSTARTDATE, 
       ACT_PRESETENDDATE, ACT_ACTSTARTDATE, ACT_ACTENDDATE, ACT_BASELINESTARTDATE, ACT_BASELINEENDDATE,
       STORE_BUDGET1, STORE_BUDGET2, STORE_BUDGET3, STORE_BUDGET4, STORE_BUDGETPLANNED,
       ACT_STATUS, 'ACT_STATUS' as Changed_Column, 
       NULL as Old_Value, ACT_STATUS as New_Value, MOD_DATE, MOD_USER
INTO #Temp1
FROM dbAuditLogMgr.dbo.AuditData
WHERE CI_ACT_STATUS = 'Y'
ORDER BY MOD_DATE
go

ALTER TABLE #Temp1
ALTER COLUMN Old_Value   varchar(255)
go

UPDATE #Temp1
SET Old_Value = dbAuditLogMgr.dbo.AuditData.ACT_STATUS 
FROM #Temp1 INNER JOIN dbAuditLogMgr.dbo.AuditData 
ON #Temp1.ACT_REF = dbAuditLogMgr.dbo.AuditData.ACT_REF 
   AND #Temp1.MOD_DATE <> dbAuditLogMgr.dbo.AuditData.MOD_DATE 
WHERE dbAuditLogMgr.dbo.AuditData.MOD_DATE = (SELECT MAX(dbAuditLogMgr.dbo.AuditData.MOD_DATE) 
                                                     FROM dbAuditLogMgr.dbo.AuditData 
                                                     WHERE dbAuditLogMgr.dbo.AuditData.MOD_DATE < #Temp1.MOD_DATE 
                                                     AND #Temp1.ACT_REF = dbAuditLogMgr.dbo.AuditData.ACT_REF)
go

UPDATE #Temp1
SET Old_Value = dbAuditLogMgr.dbo.AuditData.ACT_STATUS
FROM #Temp1 INNER JOIN dbAuditLogMgr.dbo.AuditData
ON #Temp1.ACT_REF = dbAuditLogMgr.dbo.AuditData.ACT_REF 
WHERE dbAuditLogMgr.dbo.AuditData.MOD_DATE IS NULL 
      AND #Temp1.OLD_Value IS NULL 
      AND dbAuditLogMgr.dbo.AuditData.MOD_DATE IS NULL
go

UPDATE #Temp1
SET Old_Value = dbAuditLogMgr.dbo.AuditData.ACT_STATUS 
FROM #Temp1 INNER JOIN dbAuditLogMgr.dbo.AuditData
ON #Temp1.ACT_REF = dbAuditLogMgr.dbo.AuditData.ACT_REF 
WHERE dbAuditLogMgr.dbo.AuditData.MOD_DATE IS NULL 
      AND #Temp1.OLD_Value IS NULL 
      AND dbAuditLogMgr.dbo.AuditData.TRANSACTION_TYPE = 'INSERT'
go

INSERT INTO dbAuditLogMgr.dbo.condensedData
SELECT * 
FROM #Temp1
go

quote:

---

Originally posted by gator

My database does not capture the data. The large table is pushed to my server once a day from an Oracle server.

---

quote:

---

Originally posted by gator

The big table comes from an Oracle databse that I never see. I do not have access to the Oracle server. I only get a copy of the table. The code is used on my SQL Server 2000 box.

A user can make multiple field changes at a time on the Oracle box.
My users want me to take the big table that is loaded to my server each day that has all changes from a single change and split it up into a table that shows each field that changed as its own record with the old and new data.

---