locked
Create Table using another table column value RRS feed

  • Question

  • Hi,

    I have table XYZ which holds destination column name, i want to generate CREATE TABLE query w.r.t to XYZ value.

    For Example: need to generate create table statement.

    Table XYZ

    Slno ColumnName
    1 Empno
    2 Ename
    3 Sal

    intention behind this is if my destination table layout changes , it should handle dynamically.

    Monday, May 16, 2016 5:28 PM

Answers

  • DECLARE @DynamicTableQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX) = ''

    IF OBJECT_ID('tempdb..#COLUMNDETAILS') IS NULL
    BEGIN
    CREATE TABLE #COLUMNDETAILS
    (
    ColumnName VARCHAR(100),
    DataType varchar(100)

    INSERT INTO #COLUMNDETAILS
    SELECT 'Id', 'int'
    UNION ALL
    SELECT 'Address', 'varchar(100)'
    END


     
    --Concate column name and respective datatypes
    SELECT @ColumnName= @ColumnName + ColumnName+' '
           + DataType +', '
    FROM #COLUMNDETAILS

    SET @ColumnName = LEFT(@ColumnName, LEN(@ColumnName)-1)

     
    --PIVOT query using the dynamic sql
    SET @DynamicTableQuery =
      N'CREATE TABLE dbo.Test_DSql( ' + @ColumnName + ' )'
             
    --Execute
    --PRINT @DynamicTableQuery
    EXEC sp_executesql @DynamicTableQuery


    Prashu

    • Marked as answer by Prashuts Monday, May 30, 2016 6:04 PM
    Monday, May 30, 2016 6:03 PM

All replies

  • perphas , an easier approach might be to do Select * into DestinationTable from SourcTable.

    This way you can handle all the columns, even thougg your source changes.

    And you can add indexes\constraints to your desintationtable later as needed.


    Hope it Helps!!

    Monday, May 16, 2016 5:50 PM
    Answerer
  • The cleanest way to do this is probably the SELECT * INTO table FROM source.

    If it needs to be super dynamic, with the possibility of adding ALTER COLUMN/ADD COLUMN facets, you're going to need to use dynamic SQL:

    DECLARE @dSQL NVARCHAR(MAX), @tableName SYSNAME
    
    SET @dSQL = 'SELECT * FROM ['+@tableName+']'
    
    EXEC sp_executeSQL @dSQL


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, May 16, 2016 9:27 PM
  • DECLARE @DynamicTableQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX) = ''

    IF OBJECT_ID('tempdb..#COLUMNDETAILS') IS NULL
    BEGIN
    CREATE TABLE #COLUMNDETAILS
    (
    ColumnName VARCHAR(100),
    DataType varchar(100)

    INSERT INTO #COLUMNDETAILS
    SELECT 'Id', 'int'
    UNION ALL
    SELECT 'Address', 'varchar(100)'
    END


     
    --Concate column name and respective datatypes
    SELECT @ColumnName= @ColumnName + ColumnName+' '
           + DataType +', '
    FROM #COLUMNDETAILS

    SET @ColumnName = LEFT(@ColumnName, LEN(@ColumnName)-1)

     
    --PIVOT query using the dynamic sql
    SET @DynamicTableQuery =
      N'CREATE TABLE dbo.Test_DSql( ' + @ColumnName + ' )'
             
    --Execute
    --PRINT @DynamicTableQuery
    EXEC sp_executesql @DynamicTableQuery


    Prashu

    • Marked as answer by Prashuts Monday, May 30, 2016 6:04 PM
    Monday, May 30, 2016 6:03 PM