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.
