create table dynamically in the SP

  • Hi , i have a requirement to create a table on the fly as part of the store proc by grabbing the column names and data types from another table that has 2 columns(column_name and column_datatype), any idea?

    Lets say i have TableA(column1,column2) with 100 records from which i have to create TableB with 100 columns from the values of TableA, hope that helps. Thanks

  • Quick example, recommend you add constraints to the data type columns to prevent malicious code injection.

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    --

    IF OBJECT_ID(N'dbo.TBL_TEST_DYNAMIC_CREATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_DYNAMIC_CREATE;

    -- Create the table definition table

    CREATE TABLE dbo.TBL_TEST_DYNAMIC_CREATE

    (

    TCD_ID INTIDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_TBL_TEST_DYNAMIC_CREATE_TCD_ID PRIMARY KEY CLUSTERED

    ,TCD_COL_NAME NVARCHAR(128) NOT NULL

    ,TCD_DATA_TYPE NVARCHAR(128) NOT NULL

    );

    -- Sample data

    INSERT INTO dbo.TBL_TEST_DYNAMIC_CREATE ( TCD_COL_NAME , TCD_DATA_TYPE )

    VALUES (N'COL_01',N'INT')

    ,(N'COL_02',N'INT')

    ,(N'COL_03',N'INT')

    ,(N'COL_04',N'INT')

    ,(N'COL_05',N'INT')

    ,(N'COL_06',N'VARCHAR(50)')

    ,(N'COL_07',N'VARCHAR(150)')

    ;

    -- Table name from GUID

    DECLARE @TABLE_NAME NVARCHAR(128) = N'TTBL_' + REPLACE(CONVERT(VARCHAR(128),NEWID(),0),NCHAR(45),N'');

    -- Create table template

    DECLARE @TEMPLATE NVARCHAR(MAX) = N'

    CREATE TABLE [dbo].{{@TABLE_NAME}}

    (

    {{@COLUMNS}})

    ;

    '

    DECLARE @CREATE_TABLE_STR NVARCHAR(MAX) =

    REPLACE(

    REPLACE(@TEMPLATE,N'{{@COLUMNS}}',

    STUFF(

    (

    SELECT

    NCHAR(44) + QUOTENAME(DC.TCD_COL_NAME) + NCHAR(32) + DC.TCD_DATA_TYPE + NCHAR(13) + NCHAR(10)

    FROM dbo.TBL_TEST_DYNAMIC_CREATE DC

    FOR XML PATH(''),TYPE

    ).value('(./text())[1]','NVARCHAR(MAX)'),1,1,N''))

    ,N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));

    DECLARE @SELECT_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM [dbo].{{@TABLE_NAME}};',N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));

    DECLARE @DROP_STR NVARCHAR(MAX) = REPLACE(N'DROP TABLE [dbo].{{@TABLE_NAME}};' ,N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));

    -- Verify the output

    SELECT @CREATE_TABLE_STR UNION ALL

    SELECT @SELECT_STR UNION ALL

    SELECT @DROP_STR

    /* -- Execute

    EXEC (@CREATE_TABLE_STR)

    EXEC (@SELECT_STR)

    EXEC (@DROP_STR)

    --*/

    Output

    CREATE TABLE [dbo].[TTBL_1A064981187745488F5514D7497332A4]

    (

    [COL_01] INT

    ,[COL_02] INT

    ,[COL_03] INT

    ,[COL_04] INT

    ,[COL_05] INT

    ,[COL_06] VARCHAR(50)

    ,[COL_07] VARCHAR(150)

    )

    ;

    SELECT * FROM [dbo].[TTBL_1A064981187745488F5514D7497332A4];

    DROP TABLE [dbo].[TTBL_1A064981187745488F5514D7497332A4];

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply