create table at run time. Please help!!!!

Question

create table at run time. Please help!!!!

Quang Nguyen-314267

Newbie

Points: 2
More actions
April 22, 2006 at 4:12 pm

#89926

Greeting All,
I need to create a table at run time. The table name will have the name of the sell date embedded in it.
For Ex: If the sell date is 04/20/2006 then in the stored procedure, I will create a table name sellreport_APR06 and will insert record into this table. The table will serve as history table for user to look back for history sell data.
When user run the sell report next month (05/2006), a different table (sellreport_MAY06) will be created and so on.....
Note that the sell date is entered from front end (VB app) and pass it to the stored procedure as variable.
I am trying this code but it does not work:
declare @selldate char(10)
create table sellreport_@selldate
expecting that @selldate will have MAY06, but it does not work.
Please let me know if you have any idea as for table name or any scenario for keeping history data monthly.
Thank you for your time,
ConKi.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

what happens when you need to tie all those tables together for a quarterly , semi-annual or yearly report?

you'll spend a ton of time writing reports or views which work around this flaw in design.; and it would be an ongoing thing, where every month you need similar reports, but

i disagree with your design; there is no difference in the data you are gathering except the perceived date issue; the raw data is the same format, so it should all be in the same table.everything should go into a single table, and you should consider writing a stored proc that would pull the one months worth of data based on a date variable.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 2

To answer your question, you need to use DynamicSQL to create your table on the fly. Checkout sp_ExecuteSQL in books online. I would not recommend using this in a production environment as there is a performance hit you will take.

I agree with Lowell that if your data is the same, except for date, you should use a single table. Especially if you are not working with alot of data. As you gain in rows, you could consider partitioning by table on a monthly basis and wrapping the tables with a partitioned view, using a check contraint on the date field (We use a month number/year number when we need to do this).

Quang Nguyen-314267 Newbie Points: 2 More actions · Answer 3

Thank you for your replys. I will take your suggestions into consideration. Put all data into just one table is a great idea that I did not think of. The only problem I will get is the data keeps growing. I might put an insert trigger to move old data (say 10 years old) to a history table or just delete them. I need to talk to user about this.

Thank you all,

ConKi