title: Replicate a table record with a new id value using dynamic SQL
body: <p>I have an application with a requirement that the user can "copy" a record. This will duplicate the record in the table, and the associated records in any child tables. </p>
  
  <p>I will use a trigger to execute a stored procedure to do the copy. The issue I am facing is that I want to increment the ID field for the copied record, which is also the FK in the child tables. The ID field is not a standard format, so using an increment won't work. In order to make this future-proof, I was going to use dynamic SQL to pull the columns for each table so that I don't need to modify the code if I add a new field to one of the tables. The client's system admin can also add columns to the table via the GUI, but they have no access to the SQL backend so they would need to contact us to modify the code (not ideal).</p>
  
  <p>Example:</p>
  
  <pre><code>Declare @ColumnNames varchar(2000)
  Declare @BLDGCODE char(4)
  set @BLDGCODE = '001'
  
  select @ColumnNames = COALESCE(@ColumnNames + ', ', '') + COLUMN_NAME
  from 
      INFORMATION_SCHEMA.COLUMNS
  where 
      TABLE_NAME='FMB0'
  Declare @DynSqlStatement varchar(max);
  set @DynSqlStatement = 'Insert into dbo.FMB0('+ @ColumnNames + ') 
  select * from dbo.FMB0 where BLDGCODE= ' + cast(@BLDGCODE as char(4));
  
  print(@DynSqlStatement);
  </code></pre>
  
  <p>This solves the issue for a new column being added to one of the tables. However, how can I increment the ID (BLDGCODE in this example). Is my only solution to script out the columns by name so I can increment the ID, or is there a function I am overlooking?</p>
  
  <p>Hopefully this made sense. I am an intermediate SQL user at best, so forgive the naivete if there's an obvious solution.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>So I've decided to use #temp tables to hold the record that was changed, modify the id there, and then insert back into the main table from the #temp table. This is working pretty well, with one exception. I get the following error:</p>
  
  <p>The column "FLOORID_" cannot be modified because it is either a computed column or is the result of a UNION operator.</p>
  
  <p>Below is my stored procedure. I've investigated a STUFF approach, but not sure where to insert that code. <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d86aa109-c276-4e4f-a160-edb073053980/error-computed-column-or-union-operator-while-trying-to-insert-records?forum=transactsql" rel="nofollow noreferrer" title="STUFF Post">Using STUFF with calculated column</a>. I am now back to thinking I need to call out the columns specifically for the one table with the computed column, and if we add a new field, I just need to modify this stored procedure. Anyone have any other ideas?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[LDAC_BLDGCOPY]
      @BLDGCODE CHAR(4)
  AS
  BEGIN
      SET NOCOUNT ON;
  
      --COPY BUILDING RECORD
      BEGIN
          DECLARE @ColumnNamesB0 VARCHAR(2000);
  
          SELECT *
          INTO #TEMPB0
          FROM FMB0
          WHERE BLDGCODE = @BLDGCODE;
  
          UPDATE #TEMPB0
          SET BLDGCODE = CONVERT(CHAR(4), (CAST((@BLDGCODE) AS INT) + 
  100)),
              auto_key = dbo.GetAutoKey(),
              BLDGCOPY = 0;
  
          SELECT @ColumnNamesB0 = COALESCE(@ColumnNamesB0+', ', 
  '')+COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'FMB0';
  
          DECLARE @DynSqlStatementB0 VARCHAR(MAX);
          SET @DynSqlStatementB0 = 'Insert into dbo.FMB0('+@ColumnNamesB0+') 
  select * from #TEMPB0';
  
          EXEC (@DynSqlStatementB0);
       END;
  
      --COPY FLOOR RECORDS
      BEGIN
          DECLARE @ColumnNamesL0 VARCHAR(2000);
          --DECLARE @Val INT = 
  RTRIM(CONVERT(CHAR(10),CAST(LEFT(@BL_KEY,LEN(RTRIM(@BL_KEY))-2)+1 as 
  INT)))+'01
  
          SELECT *
          INTO #TEMPL0
          FROM FML0
          WHERE BLDGCODE = @BLDGCODE;
  
          UPDATE #TEMPL0
          SET BLDGCODE = CONVERT(CHAR(4), (CAST((@BLDGCODE) AS INT) + 
  100)),
              auto_key = dbo.GetAutoKey();
  
          UPDATE #TEMPL0
          SET FLOORID_ = auto_key;
  
          SELECT @ColumnNamesL0 = COALESCE(@ColumnNamesL0+', ', 
  '')+COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'FML0';
  
          DECLARE @DynSqlStatementL0 VARCHAR(MAX);
          SET @DynSqlStatementL0 = 'Insert into dbo.FML0('+@ColumnNamesL0+') 
  select * from #TEMPL0';
           EXEC (@DynSqlStatementL0);
       END;
  
  --COPY ROOM RECORDS
       BEGIN
           DECLARE @ColumnNamesA0 VARCHAR(2000);
  --DECLARE @Val INT = 
  RTRIM(CONVERT(CHAR(10),CAST(LEFT(@BL_KEY,LEN(RTRIM(@BL_KEY))-2)+1 as 
  INT)))+'01
  
           SELECT *
           INTO #TEMPA0
           FROM FMA0
           WHERE BLDGCODE = @BLDGCODE;
           UPDATE #TEMPA0
             SET
                 BLDGCODE = CONVERT(CHAR(4), (CAST((@BLDGCODE) AS INT) + 
  100)),
                 auto_key = dbo.GetAutoKey(),
                 FLOORID = #TEMPL0.FLOORID_
           FROM #TEMPA0
                INNER JOIN #TEMPL0 ON CONVERT(CHAR(4), (CAST((@BLDGCODE) AS 
  INT) + 100)) = #TEMPL0.BLDGCODE;
           SELECT @ColumnNamesA0 = COALESCE(@ColumnNamesA0+', ', 
  '')+COLUMN_NAME
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'FMA0';
           DECLARE @DynSqlStatementA0 VARCHAR(MAX);
           SET @DynSqlStatementA0 = 'Insert into dbo.FMA0('+@ColumnNamesA0+') 
  select * from #TEMPA0';
           EXEC (@DynSqlStatementA0);
           DROP TABLE #TEMPB0;
           DROP TABLE #TEMPL0;
           DROP TABLE #TEMPA0;
       END;
   END;
  </code></pre>