• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Dynamic Table creation

I am wondering if there is a way to have a function or query in the stored procedure build in which
while check if a certain table already exists and if not create it by calling a stored procedure.

i have a job which runs every 1 hr to extract data from one table and moves it into an archive table.
The archive table grows by 10 million records per month which makes querys slower and i would like
to break the archive by month like this.

lets say a stored procedure is called to insert the row it should check based on a certain value called
inserttime if the tabel exists.

lets say the record has a inserttime of 03/31/2005 23:58:22 then it would check if table archive_032005 exists
if it does it will just insert the row. now the next record has a inserttime of 04/01/2005 01:02:32 now we check
if archive_042005 exists, since it does not we call sp_makearchivetable "042005" once it has been created we can
now insert data into the new table..

how can i code this so it does not take a huge hit, as well as how can i code the insert stored procedure in a way
that it can change the tablename based on the Value in the inserttime field
0
AlexPonnath
Asked:
AlexPonnath
  • 4
1 Solution
 
Eugene ZCommented:
see idea of Dynamic SQL
http://www.sqlteam.com/item.asp?ItemID=4599


--to see if table not exists

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test222]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
Create table test222...

end
--------------------or
if object_id('test223')is  null
Begin
 create table test223
End




0
 
AlexPonnathAuthor Commented:
how big is the performance hit ? if i have to do this every single time ?
0
 
Eugene ZCommented:
>how big is the performance hit ? if i have to do this every single time ?
if to check if table is here - - just check sysobjects - no perf effect


there is sample of code:

declare @tabl varchar(100)

set @tabl = 'archive_' + convert(char(2),getdate(),101) + convert(char(4),getdate(),120)

if object_id('+ @tabl+ ')is not  null
Begin
 -- here your you create table

end
Else
Begin
-- here your  insert into

end
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Eugene ZCommented:
little modification:

declare @tabl varchar(100)
declare @str varchar(4000)
set @tabl = 'archive_' + convert(char(2),getdate(),101) + convert(char(4),getdate(),120)
print @tabl
if object_id( @tabl) is  null
Begin
 -- here your  Create Table
 set @str= 'Create Table ' + @tabl + ' (col1 int)'

 
    exec (@str)
    goto I   ---after table created goto insert
end
Else
Begin
-- here your  insert into
    I:
    set @str='insert into ' + @tabl + ' select 1'
    exec(@str)
end
--select * from archive_042005
0
 
Eugene ZCommented:
BTW: if Sp used for creation of table

--use, for example
set @str= 'exec sp_createtable ' + @tabl
exec(@str)
0
 
ShogunWadeCommented:
Hi,

Eugene describes how to to this pretty well, but I thought I would add a few snippets (from experience of the doing same thing)

Security can be an issue with creating tables, so I personally have a sql agent job that creates monthly tables in advance. of their requirement.  The job is scheduled under a sysadmin account context.

If you want to be able to seemlessly query all the tables you may want to also dynamically create a partitioned vie across all the monthly tables.  then you can use the view as a single entry point to query the tables.

More recently I have changed my strategy to have 1 database per month with a signe table in each called Archive.... Why have I done this?   basically because very quickly with VLDBs you will hit the problem that your database becomes way to big to back up in a practical amount of time ( I deal with aprox 50million row per day).  So splitting the table into seperate databases means that at the end of one month the database for that month becomes static, It can then be reindexed and shrunk and set readonly and then backed up once.    

You will also find that using this technque, if and when disk space becomes a problem, you can detatch some of the monthly databases copy the db files to another drive (or even server) and reattach thus giving significantly greater flexibility for storage planning.

Again this is a stratagy I use.  eg: my I have 3 months of data maintained on a SCSII Raid 10,  after 3 months it is moved to SCSII Raid 5 San then finally after 9 months ends up on an IDE raid San.  When our IDE Sam is full the the data will be likely never to be queried again (in our senario) and so it will then be streamed to ofline storage.

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now