At work I maintain some databases that are updated on a daily or weekly basis from a master data source. I inherited a stored procedure that would clear the data and then import the new data, but occasionally there would be problems with the import that would leave the data table empty. Also, the new data set from the master system only includes active records, so I would not have a way to maintain data that is no longer active. So, to improve things and make my job easier I created a Microsoft SQL Server DTS (Data Transformation Services) package that would automatically insert new records and update existing records
This is SQL code to INSERT/UPDATE from a temp table into the main table and keep track of dates as well as active status.
DECLARE
@DoLoop bit,
@tClockNumber varchar(16),
@tUserName varchar(16),
@tLastName varchar(64),
@tFirstName varchar(64),
@tBirthDate varchar(16),
@tCostCenter varchar(16),
@tOrgUnitNumber varchar(16)
SET @DoLoop = 1
DECLARE TempCursor CURSOR READ_ONLY FOR
SELECT *
FROM SAPEmployeesTemp
ORDER BY UserName
OPEN TempCursor
FETCH NEXT FROM TempCursor
INTO
@tClockNumber,
@tUserName,
@tLastName,
@tFirstName,
@tBirthDate,
@tCostCenter,
@tOrgUnitNumber
WHILE @DoLoop = 1
BEGIN
/* If the record exists then update it, otherwise insert it */
IF EXISTS (SELECT * FROM SAPEmployees WHERE ClockNumber = @tClockNumber)
BEGIN
/* If the record has not changed then only update timestamp */
IF EXISTS
(SELECT *
FROM SAPEmployees
WHERE ClockNumber = @tClockNumber
AND UserName=@tUserName
AND LastName=@tLastName
AND FirstName=@tFirstName
AND BirthDate=@tBirthDate
AND CostCenter=@tCostCenter
AND OrgUnitNumber=@tOrgUnitNumber)
BEGIN
UPDATE SAPEmployees
SET IsActive=1,
UpdateCheck=getdate()
WHERE UserName = @tUserName
END
ELSE
BEGIN
UPDATE SAPEmployees
SET ClockNumber=@tClockNumber, UserName=@tUserName,
LastName=@tLastName, FirstName=@tFirstName,
BirthDate=@tBirthDate, CostCenter=@tCostCenter,
OrgUnitNumber=@tOrgUnitNumber,
IsActive=1,
UpdateCheck=getdate(),
UpdatedOn=getdate(),
UpdatedBy='UPDATE'
WHERE ClockNumber = @tClockNumber
END
END
ELSE
BEGIN
INSERT INTO SAPEmployees
SELECT @tClockNumber, @tUserName, @tLastName, @tFirstName,
@tBirthDate, @tCostCenter, @tOrgUnitNumber, 1,
getdate(), getdate(), 'INSERT'
END
/* Get the next record */
IF @@Fetch_Status = 0
BEGIN
FETCH NEXT FROM TempCursor
INTO
@tClockNumber,
@tUserName,
@tLastName,
@tFirstName,
@tBirthDate,
@tCostCenter,
@tOrgUnitNumber
END
ELSE
BEGIN
SET @DoLoop = 0
END
END
CLOSE TempCursor
DEALLOCATE TempCursor
UPDATE SAPEmployees
SET IsActive=0,
UpdatedOn=GetDate(),
UpdatedBy='DEACTIVATE'
WHERE UpdateCheck<getdate()-1></getdate()-1>
