November 22, 2017 at 1:53 am
Hello All,
I require some suggestion for one of my requirement.
I have a SQL Database where 30 tables are created automatically each per bay basis by some application.The columns in the each tables are identical
and there is n way that they are OR will be different.
Each day a new table is created and the name of the table contains date and time in it. One a table is created say for 16-Nov-2017, then the table created on 16-Oct-2017 is automatically deleted.And in this way we are keeping a month information in 30 different tables.So the database where tables
are stored is having only 30 tables at any given point of time.These tables are created ever 00:00:00 hrs in midnight.
My requirement is to make a table in a new database that will hold only one table where all the 30 days tables (for one month) will be merged and I can have information in one single table only.This table will hold data for only 30 days and once 30 days tables are merged, I will rename the table removing the date and time information from it and thus for a month I will have only one table.
Tables are created in the following format.
dbo.abcd_20170414000000. 20170414 represents the date when it was created.Here the table is created on 14-Apr-2017 at 00:00:00 hrs.
Like wise I am having table as follows.
dbo.abcd_20170414000000. 20170414
dbo.abcd_20170413000000. 20170414
dbo.abcd_20170412000000. 20170414
dbo.abcd_20170411000000. 20170414
dbo.abcd_20170410000000. 20170414
--
--
--
dbo.abcd_20170315000000. 20170414
Now I want to have a table in a separate database with a name dbo.abcd_042017 for April 2017 that will have merged information for all the 30 days in April.Same for May, Jun, July etc.The database will be with in the same SQL instance.
Can any one suggest how can I do this.
November 22, 2017 at 2:44 am
I do not agree with this design as I don't think table names should be time based.
Instead there should be an additional column in the table that contains the date and time of the data.
If redesign is completely impossible and you must do things this way then I'd suggest the following:
Create a stored procedure that builds your selects and inserts using dynamic SQL, and make use of "sys.tables" to check which tables are available that day so that you can cycle through them.
November 22, 2017 at 2:56 am
Paulo is right. You don't have any control over the structure of your application database, but if you're creating your own database, don't make the same mistakes with the design. Put everything in one table, adding an extra column if necessary to identify the date for each row.
John
November 22, 2017 at 3:07 am
Paulo de Jesus - Wednesday, November 22, 2017 2:44 AMI do not agree with this design as I don't think table names should be time based.
Instead there should be an additional column in the table that contains the date and time of the data.If redesign is completely impossible and you must do things this way then I'd suggest the following:
Create a stored procedure that builds your selects and inserts using dynamic SQL, and make use of "sys.tables" to check which tables are available that day so that you can cycle through them.
I am not familiar with dynamic SQL. Can you provide me some reference that is having some example to achieve my goal.
November 22, 2017 at 3:12 am
You can check out this link:
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
November 22, 2017 at 3:33 am
Paulo de Jesus - Wednesday, November 22, 2017 3:12 AMYou can check out this link:
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
Thanks. I will check and revert back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply