Create a table

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  November 1, 2014 at 1:16 pm

  #298016

  How to code to create a table in which there are 300 columns and name them as column1, column2...column300?

  All data type is varchar(255)

• Igor Micev

  SSC-Dedicated

  Points: 33110

  More actions

  November 1, 2014 at 5:15 pm

  #1756183

  DECLARE @dyn_sql NVARCHAR(max)

  DECLARE @table_name NVARCHAR(100)

  SET @table_name='MyTable'

  SET @dyn_sql='CREATE TABLE '+@table_name+'('+CHAR(10)

  DECLARE @i INT=0

  WHILE (@i<300)

  BEGIN

  SET @i+=1

  IF (@i<300)

  SET @dyn_sql +='Column'+CONVERT(NVARCHAR(3),@i)+' varchar(255),'+CHAR(10)

  ELSE

  SET @dyn_sql +='Column'+CONVERT(NVARCHAR(3),@i)+' varchar(255)'+CHAR(10)

  END

  SET @dyn_sql+=')'

  PRINT CAST(@dyn_sql AS NTEXT)

  Igor Micev,My blog: www.igormicev.com

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  November 1, 2014 at 5:21 pm

  #1756185

  It works great!

  Thank you. You save my life.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 1, 2014 at 5:38 pm

  #1756187

  Here is another solution with no while loop:

  declare @SQLCmd nvarchar(max),

  @TableName sysname = N'MyTable';

  with eCTE as (

  select top (300) row_number() over (order by (select null)) n from sys.all_columns

  )

  select @SQLCmd = N'create table ' + @TableName + N' (' +

  stuff((select N',' + nchar(13) + nchar(10) + N' Column' + cast(n as nvarchar) + N' varchar(255)'

  from eCTE

  for xml path(''),TYPE).value('.','nvarchar(max)'),1,1,'') + N');'

  select cast(@SQLCmd as xml);

  exec sp_executesql @SQLCmd;