Creating a new table in the same style daily/weekly mssql

Question

I have a set of data that I want to insert into a database daily, creating a new table every day or every week to have an easy historic overview.

How can I accomplish that? Should I do it in code or in the database itself?

Expecting on daily basis on a particular time? or when ever you need, that time only the data need to move into the new table? — Arulkumar, Apr 15 '16 at 08:17
You can achieve this by the [SQL Jobs](http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily) — Arulkumar, Apr 15 '16 at 08:20
Actually, it's not a very good idea for multiple reasons. If you decide to go this route anyway, pls make sure to add a check constraint to the date column. See "partitioned view" (eg here: https://technet.microsoft.com/en-us/library/ms190019%28v=sql.105%29.aspx) for more information on this pattern. — dean, Apr 15 '16 at 08:51

score 0 · Answer 1 · answered Apr 15 '16 at 08:41

Consider MySourceTable is your actual table, from there you want to move the data on daily basis, you need to create a SQL Job and use the below SP.

So you will get a daily based table with the prefix name you provide. For testing I give here MyTest_ as prefix, so today's table will come as MyTest_2016_04_15.

Sample Stored procedure:

CREATE PROCEDURE dbo.pub_DailyBasisDataMove
AS
BEGIN

    DECLARE @LogDate VARCHAR(20);
    SET @LogDate = REPLACE(CONVERT(VARCHAR(12), GETDATE(), 111), '/', '_');
    -- PRINT @LogDate

    DECLARE @ExecuteSql AS NVARCHAR (MAX) = '';
    SET @ExecuteSql = @ExecuteSql + N'SELECT * INTO MyTest_' + @LogDate + ' FROM dbo.MySourceTable'
    --PRINT @ExecuteSql

    EXEC sp_executesql @ExecuteSql

END

Note: If you want to run the job more than once in day, you need to Drop the existing table for the day and create the new.

In the same way you need to create one more SP for weekly basis and separate JOB and handle the same in the prefix content inside the SP.

Creating a new table in the same style daily/weekly mssql

1 Answers1