create table at run time. Please help.

Last Post 03 May 2006 04:03 PM by ConKi. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ConKi
New Member
New Member

--
22 Apr 2006 01:02 PM
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 selling record for the month of APRIL 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.
nosepicker
New Member
New Member

--
22 Apr 2006 03:09 PM
You need to use dynamic SQL:

DECLARE @selldate char(10), @sql varchar(1000)

SET @selldate = 'MAY06'

SET @sql = 'CREATE TABLE sellreport_' + @selldate + '(col1 int NULL, ...)'

EXEC (@sql)
ConKi
New Member
New Member

--
24 Apr 2006 06:52 AM
Thank you all,
Dynamic SQL is a great idea. I wil try it.
ConKi
cmdr_jpskywalker
New Member
New Member

--
25 Apr 2006 11:17 AM
or use a template table and do this
IF (SELECT OBJECT_ID('dbo.uspCreateSellTable')) IS NOT NULL DROP PROCEDURE dbo.uspCreateSellTable
GO

CREATE PROCEDURE dbo.uspCreateSellTable @dt DATETIME
AS
BEGIN
DECLARE
@TBName VARCHAR(128),
@sSQL NVARCHAR(4000)

--add the target database if you need to
SET @TBName = 'dbo.SellReport' + DATENAME(MONTH, @dt) + STR(DATEPART(DD, @dt))
SET @sSQL = 'SELECT * INTO ' + @TBName + ' FROM dbo.SellReportTemplate'
EXEC sp_executesql @sSQL
--you can also use EXEC (@sSQL)
END
GO

This way, you only need to maintain the template table and not worry with the ddl changes.

ConKi
New Member
New Member

--
03 May 2006 04:03 PM
Thank you cmdr_jpskywalker,
Applied your solution and it works perfectly. cool thing is I do not have to worry about ddl for the new table.
ConKi.
SQLUSA
New Member
New Member

--
07 May 2006 02:53 PM
Why don't you setup a permanent table with date as column?

Why do you need date to be part of a table name?

That is not really relational design. More like the file system.


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/articles2005/top10/
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy