Can't Create Table in SQL

Asked Mar 05 '15 at 05:04

Active Sep 15 '17 at 09:30

Viewed 88 times

I'm trying to create a table by executing the script in SP.

Portion of Creating Table:

SET @SQL = 'CREATE TABLE #TEMPS' + 
    '( ID VARCHAR(100), ' +
    'CUR_SUPVR_ID VARCHAR(100), ' +
    'CUR_SUPVR_NAME VARCHAR(100), ' +
    'CUR_SUPVR_LVL VARCHAR(100) )'

EXEC(@SQL)

But the #TEMPS was not created. Any thought?

edited Sep 15 '17 at 09:30

asked Mar 05 '15 at 05:04

Stuart

1

`Temp table` created `inside` **dynamic query** cannot be accessed `outside` of dynamic query – Pரதீப் Mar 05 '15 at 05:05
Also i don't see need of dynamic query here – Pரதீப் Mar 05 '15 at 05:07
#TEMPS name is in variable form. That is why I made it that way. – Stuart Mar 05 '15 at 05:09
As i mentioned you cannot access outside of dynamic query. Try using global temp table it will work – Pரதீப் Mar 05 '15 at 05:10
##TempTable can be accessed universally, as opposed to #TempTable is only accessible within session. – Brad Mar 05 '15 at 05:11
Also keep in mind that global temp table will fail when same code is executed in another session – Pரதீப் Mar 05 '15 at 05:15
@NoDisplayName given what you've said, if the name of the temp table is not in variable, like #TEMPS. What if two users will execute the SP that create the temp table at the same time? Will it create 2 #TEMPS for each user or first come first serve? – Stuart Mar 05 '15 at 05:17
1

@Stuart - It will create two different temp tables with session append to the name internally. But it is not the case for global temp table – Pரதீப் Mar 05 '15 at 05:20
Given that, I guess static temp table name will do. The reason I made it dynamic is to avoid the error of creating the table due to its existance. – Stuart Mar 05 '15 at 05:21
1

Which dbms is this for? – jarlh Mar 05 '15 at 07:13

Can't Create Table in SQL

0 Answers0