Creating new tables and deleting old ones

[bminaeff]

Hi all,

I was wondering how I could create a new table in SQL using a stored procedure. I have a program that needs to create a new table based on a number so for instance I would like to have TableName be a variable which I could enter Table_123 as the name. My problem is that, I can not get this to execute when I try to make it a procedure. What am I doing wrong?

CREATE TABLE TableName
(CartonID nvarchar(50),
DCIC nvarchar(50),
DoorNum nvarchar(50),
TruckNum nvarchar(50),
SealNum nvarchar(50),
SimpleTime timestamp,
SimpleDate nvarchar(50),
SortDateTime smalldatetime,
Operator nvarchar(50))

Also, completely separate from this, I have to delete tables that have not been modified in 14 days. What is the best way to accomplish this?

-Thanks
Bill

 

[RiverGuy]

To dynamically create a table, you're going to be looking at using Dynamic SQL statements. Look here for more information:

http://msdn2.microsoft.com/en-us/library/ms188001.aspx.

Although keep in mind its best not to get in the habit of relying on Dynamic SQL when you can avoid it.

To delete tables which have no modifications for a certain period of time, you can use triggers to update a LastModified field in your tables, or to populate a Metadata table with TableName and LastModified. Set up a job that queries those dates and drops any relevant tables.

 

[SQLDenis]

>>My problem is that, I can not get this to execute when I try to make it a procedure. What am I doing wrong?


you need dynamic SQL but first read this

http://www.sommarskog.se/dynamic_sql.html

>>I have to delete tables that have not been modified in 14 days. What is the best way to accomplish this?

create a modification date column on the tables, create a daily job which deletes all the tables where the max modification date is older than 14 days


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bminaeff]

Thanks Guys,

I was trying to avoid using dynamic SQL, but I thought I could do it that way.

-Bill