finding & creating table at run time

Question

finding & creating table at run time

V.Rajagopalan

Valued Member

Points: 60
More actions
January 24, 2005 at 3:46 am

#361921

how to write the query for finding the table in particular database and how to create the table in run time. it will be very useful to my project.
Thanks

V.Rajagopalan

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why do you need to create a table at run time?

Usually when you need a table for an application you just create it once and leave it there. If you need a temp table for a stored proc, you might look at this topic under the books online (table / datatype) for the table variable.

Can you tell us why you want to create a table at run time so that we may guide you in the best direction for your situation.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Just to add to Remi:

Read http://www.sommarskog.se/dynamic_sql.html . Erland's (he is one of the very few knowledgeable SQL Server MVP's IMHO) message should be very clear. Avoid dynamic SQL whereever you can, and use it only when you must.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

V.Rajagopalan Valued Member Points: 60 More actions · Answer 3

Dear Remi gregoire

Thanks for your response. what i really want is normally we create the table once at the design time. but my requirement is some of the tables has been created at the beginning of the project but at run time i want to create the table based on that year and month. i want to keep month wise and year wise table. if i open the next month the system has to check whether the table is available if it is not there it has to be created automatically based on some function.

for example:

i want to keep sales file in the name of 'TRmmyy' here mm is month, yy is year.

is there any readmade function for that .

pls give me the syntax with example

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Unless you have a few 100s of millions of rows you don't need to do that. If your queries need to run faster I suggest moving the first part of the clustered index to year (date) + month (date) + current key. This will give you even better results than what you are trying to achieve.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

Yeah, listen to Remi on this one...

But, if you stubbornly decide to do this the wrong way, here's how:

declare @TableName sysname

select @TableName = 'Table_' + convert(varchar(12), getdate(), 112)

if object_ID(@TableName) is not null

exec ('drop table ' + @TableName )

exec ('Create Table ' + @TableName +

'(Date datetime)')

Signature is NULL