Dynanic table creation

Hello friends
I want to have a trigger for creating table on dialy basis.
for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on.
please help for writing the trigger for the same.
thanks
This seems like a job for SQL Server Agent, not a trigger. I'dwrite a stored procedure to create the table, and schedule it via SQLServer Agent to run every day.
|||

Thank you very much.....
Could you please tell me how should I create dynamic table in the query? like, I have to append the today's date to a fixed string and create the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back

|||

swatib wrote:

Thank you very much.....
Couldyou please tell me how should I create dynamic table in the query?like, I have to append the today's date to a fixed string andcreate the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back

Your dynamic CREATE TABLE statement would look something like this:

DECLARE @.Sql varchar(200)
SELECT @.Sql = 'CREATE TABLE Data' + REPLACE(CONVERT(char(12),GETDATE(),113),' ','') + '(column1 varchar(10), column2 int)'
EXECUTE(@.Sql)

And you can use Enterprise Manager to access SQL Server Agent toschedule the job. Click on your server name, then chooseManagement. Under that, click on SQL Server Agent. Underthat, right-click on Jobs and choose New Job. Enter the nameyou'd like to call this job, enter a T-SQL step to EXECUTE your storedprocedure, and set the Schedule.

|||Thank you very much sir for the details reply and the solution.

Dynamically Creating Table Name and Copying To Linked Server Help

I have tables on Server A that are created on a monthly basis. A table name
is created at the beginning of each month using an algorirthm as follows:
DECLARE @.TableName varchar (25)
SET @.TableName = 'Compare' + Month + Year
I have a script that creates the table and inserts data into (Server A)
table with no problem.
However, I need to copy the table monthly from my Server A onto a linked
server - Server B. Because these tables are created using an algorithm, I'm
having a problem using the following:
SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
...because of the limitations that T-SQL has with DDL on remote servers.
Thanks,
MichaelMichael Mach (Michael.Mach@.cmaaccess.com) writes:
> I have tables on Server A that are created on a monthly basis. A table
> name is created at the beginning of each month using an algorirthm as
> follows:
> DECLARE @.TableName varchar (25)
> SET @.TableName = 'Compare' + Month + Year
> I have a script that creates the table and inserts data into (Server A)
> table with no problem.
> However, I need to copy the table monthly from my Server A onto a linked
> server - Server B. Because these tables are created using an algorithm,
> I'm having a problem using the following:
> SELECT * INTO ServerB.DB.Table FROM ServerA.DB.Table
> ...because of the limitations that T-SQL has with DDL on remote servers.
First of all: which versions of SQL Server are the two servers?
Second, what are the sizes of these tables? Why you do make new tables
each month? Why not keep year and month as a key in the table?
Knowing more about the actual business problem makes it easier to
suggestion a solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This sounds like you have re-discovered magnetic tape files. We used
to label them the same way, only we used yy-ddd as the IBM convention
30+ years ago.
Without any more details, I think you should have all the data for
several years in one schema, and when you need to archive it, use
tapes, optical disk or some other permanent storage media. The dates
in the rows will be part of your search condition, or you can have
views for each month.|||Good point. We're using SQL 2000.
The table designs and naming conventions were setup some time ago. We do
plan to go back and redesign this.
I did find a solution though - that is to create the name of the table and
table schema on the destination table, then insert into this table from the
source table.
Thanks!
Michael
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF3E45EBBYazorman@.127.0.0.1...
> Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> First of all: which versions of SQL Server are the two servers?
> Second, what are the sizes of these tables? Why you do make new tables
> each month? Why not keep year and month as a key in the table?
> Knowing more about the actual business problem makes it easier to
> suggestion a solution.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Michael Mach (Michael.Mach@.cmaaccess.com) writes:
> Good point. We're using SQL 2000.
> The table designs and naming conventions were setup some time ago. We do
> plan to go back and redesign this.
Good. :-)

> I did find a solution though - that is to create the name of the table
> and table schema on the destination table, then insert into this table
> from the source table.
Glad to hear that you got it working!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx