Difference between CREATE LOCAL TEMPORARY COLUMN TABLE and CREATE LOCAL TEMPORARY TABLE

[kgaurav2k14]

Hi Friends,

I came across CREATE LOCAL TEMPORARY TABLE and found that we can do delete and update operations on it whereas we cannot do any modification to the table created by CREATE LOCAL TEMPORARY COLUMN TABLE.

which one is better for usage in the SQL stored procedure and why? What if I always use CREATE LOCAL TEMPORARY TABLE? Is there any disadvantage?

Thanks,

Gaurav

[Sz_An]

Hi Gaurav,

Thank you for visiting SAP Community to get answers to your questions.

If you're hoping to connect with readers, please consider adding a picture to your profile as it encourages readers to respond to you. Here's how to do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS.

Since you asked a question, don't forget to mark the answer that helped you most and is correct.

Best Regards,
Anna Szalay

[Vitaliy-R]

Hi Gaurav. Seems there is an issue with the 2.0 SPS3 documentation because it says that the COLUMN table should be created by default, but I checked and indeed it is ROW table created in case of LOCAL TEMPORARY...

CREATE LOCAL TEMPORARY COLUMN TABLE "#TEST_TABLE1" (ID INTEGER, NAME VARCHAR(10)); 
CREATE LOCAL TEMPORARY  TABLE "#TEST_TABLE2" (ID INTEGER, NAME VARCHAR(10)); 
CREATE LOCAL TEMPORARY ROW TABLE "#TEST_TABLE3" (ID INTEGER, NAME VARCHAR(10)); 


SELECT TABLE_NAME, "IS_COLUMN_TABLE" FROM M_TEMPORARY_TABLES
WHERE TABLE_NAME LIKE '%TEST%'
ORDER BY 1;

returns

TABLE_NAME  |IS_COLUMN_TABLE|
------------|---------------|
#TEST_TABLE1|TRUE           |
#TEST_TABLE2|FALSE          |
#TEST_TABLE3|FALSE          |

And per https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d58a5f75191014b2fe92141b... indeed row temporary table allows you to do Delete/Update/Upsert, that is not possible on column temporary table.

The description was fixed in the documentation for SP 04: "If neither ROW nor COLUMN is specified, the default ROW for temporary tables (local and global), and COLUMN for non temporary tables." https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20d58a5f75191014b2fe92141b...