Dynamically create table

• johmar

  SSC Veteran

  Points: 225

  More actions

  September 17, 2002 at 4:44 am

  #79706

  Does anyone know how to dynamically create a table when you do not know in advance the number of columns or the column names?

  I have been trying to do the following without any luck on SQL Server 7

  DECLARE @Column1nvarchar(8)

  DECLARE @Column2nvarchar(8)

  DECLARE @sqlnvarchar(100)

  SET @Column1 = 'First'

  SET @Column2 = 'Second'

  SET @sql = 'CREATE TABLE Dynamic (' + @Column1 +' as int, '

  SET @sql = @sql + @Column2 +' as int)'

  EXEC (@SQL)

  I get the following error for the EXEC command

  Incorrect syntax near ')'

• Clive Strong

  SSChampion

  Points: 11222

  More actions

  September 17, 2002 at 5:02 am

  #436644

  Hey Johmar,

  You have your CREATE TABLE statement slightly wrong.

  When you define the Column names, remove the 'as' keyword.

  You only need

  CREATE TABLE Dynamic (First int, Second int)

  With that removed, it works!

  Clive Strong

  clivestrong@btinternet.com

• johmar

  SSC Veteran

  Points: 225

  More actions

  September 17, 2002 at 5:08 am

  #436645

  Bugger Me it works. I can't believe it was as simple as that.

  Thanks cstrong... your a real life saver 🙂