Click here to Skip to main content
16,022,298 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have done some internal manipulations in the code and now i have a grid view with certain number of values like name,date of day1,day2,day3...day31.
So assume, i have 10 rows of data. Now i want to create a table dynamically with these columns as headers and rest rows as table data then save the table with month's name in database.
Note that the dates will change as per the month.

What I have tried:

I dont have any idea to how to do so. Please help me with some Ideas.
Posted
Updated 12-Jun-18 5:48am
Comments
Maciej Los 12-Jun-18 9:30am    
Can you be more specific and provide more details?

1 solution

Create a table in your database that has the desired schema. It doesn't have to contain any data. (Allowing NULL values will accommodate months that don't have 31 days.)
SQL
CREATE TABLE SCHEMA_TEMPLATE
(
    NAME nvarchar(255) NULL,
    Day1 DATE NULL,
    ...
    Day31 DATE NULL
) ON PRIMARY;


Then, write a stored proc that will create the desired data table that has the same schema as the SCHEMA_TEMPLTE table. Pass the desired table name to it (for instance, "May2018"):

SQL
CREATE PROCEDURE dbo.sp_CreateTableForMonth
    @tableName nvarchar(50)
AS
BEGIN
    -- this query will duplicate the schema of the SCHEMA_TEMPLATE table.
    DECLARE @query nvarchar(MAX) =
    'SELECT TOP 0 * INTO '+@tableName+' FROM dbo.SCHEMA_TEMPLATE';
    EXEC sp_executesql @query;
END


After that, it's a simple matter to put your data into the table from your code.

===================================

I would revisit your schema as well. Seems to me there might be a better way to represent the data, such as just putting all the data into the same table, and using a MERGE command to update the table. This beats having one table for every month...
 
Share this answer
 
v5
Comments
Member 13858616 13-Jun-18 4:39am    
Hi john,

thank you so much for your solution... I appreciate if you could explain the last case you mentioned that is using MERGE?
So that i would have only one table right.. So could you please share your idea on that?
Member 13858616 13-Jun-18 4:52am    
Actually this day1,day2,day3...day3 columns names are not constant. Its different for every month like 1/02/2018(monday), 2/02/2018(Tuesday)...etc...
So these values will automatically popup in my grid views as per my code. I just wanted to create a table with this columns(columns of my Grid view). So i dont just want to create a table dynamically, i want to create a table dynamically with fields taken from my Grid view.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900