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>

Tags: Blogger T-SQL Code

Published: 2008-03-11