none
Equivalent of on commit preserve rows in SQL

    Question

  • I'm creating a database in SQL that had been created in ORACLE years ago. I'm running into one problem though.

    In oracle I had a statement like this:

    CREATE GLOBAL TEMPORARY TABLE MATR_RPT
    (PERCENTAGE                             varchar2(40),
    NUM_OF_CONTRACTS			NUMBER,
    NUM_OF_CONTRACTS_C			NUMBER
    )
    on commit preserve rows;

    My SQL Statement looks like this:

    CREATE TABLE ##MATR_RPT
    (PERCENTAGE                             varchar(40),
    NUM_OF_CONTRACTS			float,
    NUM_OF_CONTRACTS_C			float
    )
    on commit preserve rows;

    But there is no "on commit preserve rows" in SQL. What would I do here to essentially do the same thing? Or does the tempDB in SQL not act like the temporary database in oracle? Do I even need this commit preserve rows? If I'm preserving rows what's the difference between just using a standard table?

    Thanks,


    • Edited by DCDeez Friday, October 18, 2013 4:42 PM
    Friday, October 18, 2013 4:27 PM

Answers

  • Hi,

    What is the exact requirement with this table? There is no clause as "ON COMMIT PRESERVE ROWS" in SQL Server. However SQL Server allows you to create temporary tables that hold data temporarily. Local temporary tables (using one #) hold data until the session is closed and global temporary tables (using two #) hold data until all existing sessions are closed. In addition to that you can declare table variable and use if it is required only for particular scope.

    ---------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    • Marked as answer by DCDeez Friday, October 18, 2013 5:50 PM
    Friday, October 18, 2013 4:48 PM

All replies

  • Friday, October 18, 2013 4:35 PM
  • Hi,

    What is the exact requirement with this table? There is no clause as "ON COMMIT PRESERVE ROWS" in SQL Server. However SQL Server allows you to create temporary tables that hold data temporarily. Local temporary tables (using one #) hold data until the session is closed and global temporary tables (using two #) hold data until all existing sessions are closed. In addition to that you can declare table variable and use if it is required only for particular scope.

    ---------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    • Marked as answer by DCDeez Friday, October 18, 2013 5:50 PM
    Friday, October 18, 2013 4:48 PM
  • Dinesh,

    I was just confused on the "On commit preserve rows" line, because to me it looks like I could create the SQL table just by removing the "on commit preserve rows" line. But I just wanted to make sure this didn't change what the statement did.

    Thanks,

    Friday, October 18, 2013 5:35 PM
  • Hi,

    As per my knowledge "ON COMMIT PRESERVE ROWS" preserves records until end of the session (My Oracle knowledge is poor, my assumption could be wrong). In that case, what statement does is, create a temporary table which is session specific. Implementation of same with SQL Server is done with local temporary table using one # sign and I do not think that it changes what current statement does.

    ---------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    Friday, October 18, 2013 5:54 PM