title: Dynamic Table Creation in T-SQL
body: <p>our team of developers add user-defined fields to many of our tables.  At times, I need to duplicate a table, for the creation of a new company in our database.</p>
  
  <p>I used to copy each table into a temporary table using a where clause, selecting one of our companies that is closest related to the new company.  I would then update the temp table with the new company  number, then pump the temp table back into the original table.  I needed every field name to do this.
  Because we seem to do this quite often, and the field names change (new ones added), I am trying to create a store procedure that is more dynamic.
  After much research, I've developed the following procedure.  But I keep getting this error on the final step:</p>
  
  <blockquote>
    <p>Msg 215, Level 16, State 1, Line 100
    Parameters supplied for object 'HQCO'
    which is not a function. If the
    parameters are intended as a table
    hint, a WITH keyword is required.</p>
  </blockquote>
  
  <p>Any help here would be appreciated.
  I have verified that the variable <code>@BuildStatement</code> has the actual field names with comma separators.  I just want to use this variable in lieu of listing my field names.</p>
  
  <pre><code>USE [Viewpoint]
  GO
  
  /****** Object:  StoredProcedure [dbo].[udCreateNewCompany]    Script Date: 03/04/2011 09:17:02 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  /*****NOTE*******************************/
  /*You must grant permission to a stored procedure 
  Grant all on dbo.udCreateNewCompany  to public  */ 
  
  
  CREATE    proc [dbo].[udCreateNewCompany] 
  
  
  @OldCo bCompany,
  @NewCo bCompany
  
    as
  
    set nocount on
  
   -- create #tmp file to hold sp_columns values 
   Create table #tmp (
   TABLE_QUALIFIER varchar(40),
   TABLE_OWNER varchar(20),  
   TABLE_NAME varchar(40),  
   COLUMN_NAME varchar(40),  
   DATA_TYPE int,  
   TYPE_NAME varchar(20),  
   PREC int, LENGTH int,  
   SCALE int, RADIX int,  
   NULLABLE char(4),  
   REMARKS varchar(128),  
   COLUMN_DEF varchar(40),  
   SQL_DATA_TYPE int,  
   SQL_DATETIME_SUB int,  
   CHAR_OCTET_LENGTH int,  
   ORDINAL_POSITION int,  
   IS_NULLABLE char(4),  
   SS_DATA_TYPE int)
   --************************************************************** 
   -- create #Fields to hold field names
   create table #Fields(
   FieldName char(40) null
   ) 
  
  -- HQCO Headquarters Company
  -- create a temporary table of HQCO values 
  
  select *  into #HQCO
  from           HQCO
  where HQCo=@OldCo
  --update temporary file with new company number
  update #HQCO
  set HQCo=@NewCo
  
  
   Set nocount on
   -- create a file of all the field name for the HQCO table
   Insert #tmp Exec sp_columns HQCO        
   -- delete the system key field
   delete #tmp where COLUMN_NAME='KeyID'
  
  declare @ColumnName as char(40)
  declare @BuildStatement as varchar(8000)
  set     @BuildStatement = ' '
  -- LOOP through the #tmp file to read the column names to build a "@BuildStatement"
  DECLARE Mycursor cursor forward_only read_only FOR
  SELECT  COLUMN_NAME
  FROM   #tmp
  
  open Mycursor
  fetch next from Mycursor into 
  /* load field value(s) retrieved from table into variable(s) */
   @ColumnName 
  
  while @@fetch_status=0
  
  begin
  set @BuildStatement =  rtrim(@BuildStatement) + rtrim(@ColumnName) + ','
  insert into #Fields(FieldName)
  select @ColumnName
  
   fetch next from Mycursor into 
  /* load field value(s) retrieved from table into variable(s) */
   @ColumnName  
   end
  
  
  -- @BuildStatement looks something like this HQCo,Name,Address,
  -- we need to remove that last comma
  
  create table #Holdit(
  FieldList varchar(8000) null
  )
  
  insert into #Holdit(FieldList)
                                 select @BuildStatement
  
  
  
  declare @LastComma as numeric(4,0)
  set     @LastComma=(select LEN(@BuildStatement) -1)
  
  
  
  set @BuildStatement=SUBSTRING(@BuildStatement,1,@LastComma)
  
  
  insert into HQCO( @BuildStatement )
  select  @BuildStatement from #HQCO
  
  
  
  
  GO
  </code></pre>