create table run time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

create table run time

Author

Topic

joseln
Starting Member

11 Posts

Posted - 2006-05-07 : 22:55:11

i create a table dynamic so that i use varaiable, when i execute it, it doesnt create it. eg/ as below

DECLARE @SQL NVARCHAR(2000)

SET @SQL = N'CREATE TABLE #TEST_CREATE1 '
SET @SQL = @SQL + ' ( ID INT, NAME VARCHAR(100))'

EXEC (@SQL)

but this doesnt create table?. i am not sure how many columns in a table, that will be added at runtime.

any syntax prob? or create table statement will not work in this way when select, insert all working this way?.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 02:27:06

The table is created but it only exists within the scope of exec(). Which means it is created and then drop when exec() exit.

You can use a global temp table.

create table ##test_create1
(
    ID   int,
    NAME varchar(100)
)

Why do you need to create a temp table dynamically ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-08 : 04:07:02

you can not access the local temp table outside the scope of dynamic sql , you need to create the global temp tables...

Read on Bol for more details..

Local Temp Table Example


DECLARE @SQL NVARCHAR(2000)

SET @SQL = N'CREATE TABLE #TEST_CREATE1 '
SET @SQL = @SQL + ' ( ID INT, NAME VARCHAR(100))'
SET @SQL = @SQL + ' Select * from #TEST_CREATE1 '

EXEC (@SQL)

Global Temp Table Example


DECLARE @SQL NVARCHAR(2000)

SET @SQL = N'CREATE TABLE ##TEST_CREATE1 '
SET @SQL = @SQL + ' ( ID INT, NAME VARCHAR(100))'


EXEC (@SQL)

Select * From ##Test_Create1

Drop Table ##Test_Create1

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources