Use Temp Tables to Easily Insert Effective-dated Rows

MS SQL Server Temp Table

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

Voila! You are done. Wasn’t that easy?
Have you faced a situation where this can be used? Do you have a better solution that could be used here?

This was done on MS SQL Server.

Tags: , ,
Subscribe to Comments RSS Feed in this post
10 Responses
  1. Nicely done…Recommended!!

  2. This is nice and can we do it in Oracle DB, if yes, what will be the SQL for that?

    • Hi Venkat,

      Glad that you found it to be nice.

      Yes, you can certainly do this in Oracle.
      The temporary table creation would look similar to this.

      CREATE GLOBAL TEMPORARY TABLE TMP1
      ON COMMIT PRESERVE ROWS
      AS
      SELECT * FROM 
      PS_CURRENCY_CD_TBL A
      WHERE ...

      The temporary table structure (not data) will be retained by Oracle even after you disconnect. So you will have to manually drop them.

      As I do not have access to an Oracle DB now, I’ve not tried this myself. If you happen to try it, please post the working scripts.

  3. It is really helpful.

  4. Hi rakesh ….It is really good ….very helpful to freshers like me…..pls try to give more examples

  5. INSERT INTO ps_currency_cd_tbl 
    SELECT 
      currency_cd
    , DATE('2015-01-01') AS new_effdt, 'I' AS new_eff_status
    , descr, descrshort, country, cur_symbol, decimal_positions, scale_positions, syncid
    , CURRENT_TIMESTAMP AS lastupddttm
    FROM ps_currency_cd_tbl
    WHERE currency_cd IN ('ABC', 'XYZ')
    ;

    You can review the data by commenting out the first line, or selecting from ps_currency_cd_tbl prior to executing “commit;”

    (Perhaps I’m missing something with the temp table?)

    P.S. There are database triggers to handle the synced field.

    • @Mike You aren’t missing anything.

      I was a fan of using temp tables for everything. So when this requirement came, guess what would have come to my mind? 🙂

      The temp table method could save you from listing all fields for a wider table. Other than that I don’t see any merit over your method.

Leave a Reply to Rakesh Subhagan Cancel reply

Your email address will not be published. Required fields are marked *

*
*