How to Use Temp Tables to Easily Insert Effective-dated Rows

Your business hands out a list of currencies that are used at your company and asks you to inactivate all the remaining ones in PeopleSoft. This is being done so that, users are not overwhelmed with the large set of currencies to choose from.
How do you do this quickly?
Well, here’s what we did.
As this is a fairly large set of data, we didn’t want to do it manually from the front end. Inactivating all non-used currencies in the PS_CURRENCY_CD_TBL table from the backend looked more promising. Since this is an effective dated table, we had to insert a new effective dated row for all currencies that were to be made inactive.
We used the below query to check what currencies were going to be inactivated. It will list down all currencies that are Active as of today and are not present in the list.

SELECT *
FROM PS_CURRENCY_CD_TBL A
WHERE
A.EFF_STATUS = 'A'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) 
               FROM PS_CURRENCY_CD_TBL A_ED
               WHERE A.CURRENCY_CD = A_ED.CURRENCY_CD
               AND A_ED.EFFDT <= GETDATE())
AND CURRENCY_CD NOT IN
        ('USD',
        'GBP',
        'INR',
        'JPY',
        'INR',
        'EUR',
        'AUD')

This SQL statement would insert the rows that we are interested in into a
 temporary table (#TMP1) where we can do all what we need. 

SELECT * INTO #TMP1
FROM PS_CURRENCY_CD_TBL A
WHERE
A.EFF_STATUS = 'A'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) 
               FROM PS_CURRENCY_CD_TBL A_ED
               WHERE A.CURRENCY_CD = A_ED.CURRENCY_CD
               AND A_ED.EFFDT <= GETDATE())
AND CURRENCY_CD NOT IN
        ('USD',
        'GBP',
        'INR',
        'JPY',
        'INR',
        'EUR',
        'AUD')

Now that we have all our rows in the Temp table, we will update the effective date, effective status and the last updated date time so that these currencies become inactive.

UPDATE
#TMP1
SET EFFDT = '01-OCT-2012',
EFF_STATUS = 'I',
LASTUPDDTTM = GETDATE()

There’s time now for a quick review to make sure everything is as expected.

SELECT * FROM
#TMP1

Now we insert the rows from the temporary table back into the original table. Since #TMP1 is a temporary table, it will not exist once you disconnect the current session.

INSERT INTO PS_CURRENCY_CD_TBL
SELECT * FROM #TMP1