title: T-SQL Dynamic table create
body: <p>So this is kind of a follow on from my last question</p>
  
  <p>I have a string that looks like this:</p>
  
  <pre><code>Acc_id,Field label,Data point
  </code></pre>
  
  <p>I'd like to create a table from the above string using somthing like </p>
  
  <pre><code>CREATE TABLE #temp
      (Acc_id         NVARCHAR(MAX), 
       Field label    REAL,
       Data point     REAL) 
  </code></pre>
  
  <p>The commas seperate the columns - it needs to be dynamic so if more columns appear they get created in the table.</p>
  
  <p>UPDATE: 
  this is what I have thus far from the net but i need to make the first column NVARCHAR and the rest REAL rather than the other way round.</p>
  
  <pre><code>  declare @path NVARCHAR(MAX)
      SET @path = 'c:\temp\Book2.txt'
  
          declare @execSQL nvarchar(1000)
          declare @tempstr varchar(1000)
          declare @col varchar(1000)
          declare @table nvarchar(1000)
  
          -- Create a temp table to with one column to hold the first row of the csv file
  
  
  
            CREATE TABLE #tbl (line VARCHAR(1000))
             SET @execSQL = 
                  'BULK INSERT #tbl  
                  FROM ''' + @path + '''  
                  WITH (  
                           FIELDTERMINATOR ='','',
                           FIRSTROW = 1,  
                           ROWTERMINATOR = ''\n'',
                           LASTROW = 1 
                        )         
                 ' 
  
             EXEC sp_executesql @stmt=@execSQL 
  
          update #tbl set line = REPLACE(line,' ','_') where line like '% %'
  
  
             SET @col = ''
             SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ',')) FROM #tbl)
             DROP TABLE #tbl
             WHILE CHARINDEX(',',@tempstr) &gt; 0
              BEGIN           
  
                 SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1))) + ' varchar(100),'     
  
                 SET @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr)) 
              END
              SET @col = @col + @tempstr + ' real'
  
             IF Object_id('tempdb..##temptable') IS NOT NULL 
             DROP TABLE #temptable 
  
             SET @table = 'create table ##temptable (' + @col + ')'
  
             EXEC sp_executesql @stmt=@table
  
  
          -- Load data from csv
             SET @execSQL = 
                  'BULK INSERT ##temptable
                  FROM ''' + @path + '''  
                  WITH (  
                           FIELDTERMINATOR ='','',
                           FIRSTROW = 2,  
                           ROWTERMINATOR = ''\n''              
                        )         
                 '  
  
             EXEC sp_executesql @stmt=@execSQL 
  </code></pre>
  
  <p>Thank you
  Rob</p>