building up dynamic column creation within procedure

  • Hi Professionals

    I have a procedure that passes in 1:CSV file, 2:Table_name 3:Column_count

    The column_count can vary and this is where I am trying to build the mid part of my create table statement within the procedure.

    I am iterating through the records and trying to build however many mid columns there needs to be in the create table statement and cannot figure it out. I am almost there with my code.

    any ideas professionals, any help would be greatly appreciated

    here is my code with output underneath

    USE [TestData]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER procedure [dbo].[importspreadsheet]

    /*Passed in variables from PHP script*/

    @importedfile nvarchar(50), /* The csv filename EG test.csv */

    @table_name nvarchar(100), /* The tabke name to be created */

    @column_count nvarchar(50) /* The total number of columns from spreadsheet */

    AS

    BEGIN

    declare @sql nvarchar(1000),/* The first part of the query build */

    @fullquery nvarchar(1000),/* The full joined query */

    @sqldrop nvarchar(1000),/* The drop table command */

    @intflag int = 0,/* The loop counter for looping through total number of columns minus 1 */

    @midcolumns nvarchar(50) = 'colname',/* The middle columns default name which will have intflag variable appended */

    @lastcolumn nvarchar(50);/* The last column to close the Create Table query */

    --set @sqldrop = 'DROP TABLE dbo.' + quotename(@table_name);

    /*Building first columns of the create table statement here which are always going to be there*/

    set @sql = 'create table dbo.' + quotename(@table_name, '[')

    + '(software_manufacturer nvarchar(max) null,

    product_name nvarchar(max) null,

    product_version nvarchar(max) null,';

    /*Loop through and create the middle columns based on the column_count variable,

    Make sure to do LESS THAN column_count and

    NOT LESS THAN OR EQUAL TO so we can close final column outside of loop*/

    while (@intflag < @column_count)

    begin

    --print @intflag

    set @intflag = @intflag +1;

    set @midcolumns = 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,';

    print @midcolumns;

    end

    /*Create last column in the create table statement*/

    set @lastcolumn = 'lastcolumn nvarchar(max) null);';

    /*Join the full create statements together to create dynamic table*/

    SET @fullquery = (@SQL + @midcolumns + @lastcolumn)

    print (@fullquery)

    --exec (@sqldrop)

    --exec (@sql)

    END

    colname[2] nvarchar(max) null,

    colname[3] nvarchar(max) null,

    colname[4] nvarchar(max) null,

    colname[5] nvarchar(max) null,

    colname[6] nvarchar(max) null,

    colname[7] nvarchar(max) null,

    colname[8] nvarchar(max) null,

    colname[9] nvarchar(max) null,

    colname[10] nvarchar(max) null,

    colname[11] nvarchar(max) null,

    colname[12] nvarchar(max) null,

    colname[13] nvarchar(max) null,

    colname[14] nvarchar(max) null,

    colname[15] nvarchar(max) null,

    colname[16] nvarchar(max) null,

    colname[17] nvarchar(max) null,

    colname[18] nvarchar(max) null,

    colname[19] nvarchar(max) null,

    colname[20] nvarchar(max) null,

    colname[21] nvarchar(max) null,

    colname[22] nvarchar(max) null,

    colname[23] nvarchar(max) null,

    create table dbo.[newtable](software_manufacturer nvarchar(max) null,

    product_name nvarchar(max) null,

    product_version nvarchar(max) null,colname[23] nvarchar(max) null,lastcolumn nvarchar(max) null);

  • It looks like you are missing concatenation

    set @midcolumns = @midcolumns + 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,';

    As a separate note, I don't think you can create a column name like column[0]

    [font="Calibri"]Raj[/font]
  • This might be more efficient for creating the columns also you would need to put the ColNameXX into the square brackets like [ColName1] etc.

    This will generate upto 256 columns in ant dataset.

    DECLARE @Cols int = 20

    DECLARE @ColList varchar(max)

    ;with L0

    AS (SELECT n From (VALUES(1),(1)) b(n)) --2

    ,L1

    AS (SELECT a.n From L0 a CROSS JOIN L0 b) --4

    ,L2

    AS (SELECT a.n From L1 a CROSS JOIN L1 b) --16

    ,L3

    AS (SELECT a.n From L2 a CROSS JOIN L2 b) --265

    ,CTE_InLineNumbersTable

    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Number FROM L3)

    select

    @ColList= Convert(varchar(max)

    ,stuff(

    (SELECT ', '+Quotename('colname'+convert(varchar,Number),'[')+' varchar(max)'+cHAR(10)

    FROM

    CTE_InLineNumbersTable

    Where Number Between 2 and @Cols

    for xml path (''))

    ,1,1,' '))

    Print @ColList

    the output here is

    [colname2] varchar(max)

    , [colname3] varchar(max)

    , [colname4] varchar(max)

    , [colname5] varchar(max)

    , [colname6] varchar(max)

    , [colname7] varchar(max)

    , [colname8] varchar(max)

    , [colname9] varchar(max)

    , [colname10] varchar(max)

    , [colname11] varchar(max)

    , [colname12] varchar(max)

    , [colname13] varchar(max)

    , [colname14] varchar(max)

    , [colname15] varchar(max)

    , [colname16] varchar(max)

    , [colname17] varchar(max)

    , [colname18] varchar(max)

    , [colname19] varchar(max)

    , [colname20] varchar(max)

    Be careful about using the Varchar(MAX) for each column definition.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • thank you that worked a treat

Viewing 4 posts - 1 through 3 (of 3 total)

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