Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: Create table dynamically

    Hello,

    I want to create a table at run time from another table that stores the column names and datatypes to use against those columns. This has been done in sql server , however am not able to find its equivalent in postgres . Below is the script where WRKTABLE is the table that stores the Columns and Datatype for the new table (FINALTABLE):

    DECLARE @mycol AS NVARCHAR(MAX);
    DECLARE @my_create_query AS NVARCHAR(MAX);
    BEGIN
    select @mycol = STUFF((SELECT ',' + QUOTENAME(COLUMNID) + ' [numeric](28, 0),' + QUOTENAME(COLUMNAME) + ' ' + DATATYPE
    FROM WRKTABLE
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    , 1, 1, '');
    SELECT @my_Create_query ='CREATE TABLE [dbo].[FINALTABLE]( [ID] [numeric](20, 0)' + @cols + ') ON [PRIMARY]'
    execute(@my_Create_query);
    END;


    Thank you so much for the help!!

    Thanks,
    Zubi

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Microsoft SQL Server code that you posted isn't syntactically correct. It can't work as posted, and even if you correct the syntax errors I'm pretty sure that it won't do what you want.

    Please post the actual code that you want us to convert for you, and some sample data that you've tested to be sure it works on Microsoft SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2013
    Posts
    17
    Hi Pat,

    I am using 2008 version of Sql Server. I have now attached the detailed example with comments and sample data.

    Please refer to the doc file in the attachment. thank you very much for the help

    Zubi
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •