Hot to Create Dynamic Table??

Question

Post reply

Hot to Create Dynamic Table??

Ezhilan

Ten Centuries

Points: 1319
More actions
August 10, 2007 at 3:20 am

#121026

Hi,
How can I create a Dynamic table in procedure....
--------------------------------------
create procedure t1 as
begin
create table tt1+cast(@@spid as varchar)
(
tcol1 varchar(10)
)
select * into temp from tt1+cast(@@spid as varchar)
drop table tt1+cast(@@spid as varchar)
end
------------------------------------------
It throws out error as follows....
Server: Msg 170, Level 15, State 1, Procedure t1, Line 6
Line 6: Incorrect syntax near '+'.
-----------------------------------
How can I create it....
Regards,
Ezhilan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

you will have to write a dynamic query in the procedure for this.

"Keep Trying"

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Ezhilan

Since you are dropping the table at the end, is there any reason why you can't just use a temporary table (#MyTable)?

John

Ezhilan Ten Centuries Points: 1319 More actions · Answer 3

Hi John Mitchell,

Due to performance issue, we are replacing all the temporary tables (# tables) with Permanent tables.

And, we need to execute the procedure simultaneously for different parameters. While doing that, we are getting Deadlock errors. As we are using lots of Updates in the Temp table , we are not able to create proper Indexes on it. We tried using DBCC_trace also. Unfortunaltely, we could not sort out this issue. So, we planning to build some Dynamic table and handle it using SP_ID.

Can you please tell me how to create a Dynamic table based on the above example. I am novice to SQL Server.

hr_sn Ten Centuries Points: 1092 More actions · Answer 4

If this is the only solution then I would say, do what Chirag has suggested, though I'm not a big fan of dynamic sql. Try using sp_executesql to run dynamic sql if you are executing from within SP.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Don't see where do you expect to gain performance.

If you'll do what you want to do performance will only suffer.

What you need to do is to revise your "lots of Updates" approach and write effective SQL code.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

I agree... as everyone knows, temp tables live in TempDB. TempDB's recovery mode is set to SIMPLE which uses minimal logging. No performance advantage to shifting temp tables to permanent tables especially if you create the correct indexes (including a primary key) on the temp tables.

If you're talking about replacing all table variables, I agree, because table variables do not and can not be made to use statistics.

Also, no big advantage in using sp_ExecuteSQL over plain old dynamic SQL for batch processes like this... only helps if you are trying to exec repetative code as if from a GUI. If you have a batch process that execs repetative code as if from a GUI, then you have bigger problems than you know (RBAR on steroids). The "effective SQL code" that Serqiy spoke of is good solid set-based code.

To summarize, it's not likely that you will see any performance gain in converting the temp tables to permanent (or working) tables. In fact, you may actually see a decrease in performance especially if RBAR is involved.

("RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row")

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ezhilan Ten Centuries Points: 1319 More actions · Answer 7

Can anyone of you...give me a syntax for creating Dynamic Table..??

Sergiy SSC Guru Points: 110208 More actions · Answer 8

EXEC('CREATE TABLE ' + {build table name} + '

......

')

If you don't believe smart and experienced people go and hit the wall.

_____________
Code for TallyGenerator

Ezhilan Ten Centuries Points: 1319 More actions · Answer 9

Sorry..if you have misunderstood me...

I wanted just the syntax for that...thats it.

Anyways, thanks for your advice..