Thursday, April 3, 2014

Creating "Dynamic TABLE" by using T-SQL Script


Some times there will be a situation, Your manager/front end colleges feel how good our code generate a dynamic table to load the appropriate
data on the fly.
I want my script to generate like MyTestTable_TodayDate with few columns as requested...
It looks as below
        CREATE TABLE MyTestTable_20140403
                        (
                             Sno INT
                            ,InsertDate DATETIME
                            ,EntryBy VARCHAR(30)
                        )
                      
-- You can use the following script to do that.
Script :
        -- Declare Dynamic Value, In this scenario it is date
        DECLARE @DynamicValue VARCHAR(10)
        -- Just formatting how I would like to see the dynamic value
        SET @DynamicValue = Replace(CONVERT(VARCHAR(10),GETDATE(),120),'-','')
        -- Check what date format will be adding...
   -- Note Test only:     SELECT @DynamicValue

        DECLARE @str VARCHAR(1000)  -- Make sure the length of the string you are passing

        SET @str = 'CREATE TABLE MyTestTable_'+@DynamicValue+'
                    (     Sno INT
                        ,InsertDate DATETIME
                        ,EntryBy VARCHAR(30)
                    )'
    -- Note Test only: It tells the final creating table syntax
        PRINT (@str)
        -- Which is the execution command, Creates the dynamic Table
        EXEC (@str)