title: Using a variable for the table name in an INSERT statement
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/6877624/is-there-a-way-to-specify-table-name-as-a-string">Is there a way to specify table name as a string?</a>  </p>
  </blockquote>
  
  
  
  <p>The following script will be executed without error:</p>
  
  <pre><code>DECLARE @SourceDB sysname, @SourceTable sysname, 
          @UserID NVARCHAR(500), @DMLType CHAR(1), @SourceIdent uniqueidentifier, @ChangedData XML,
          @SQL as NVARCHAR(MAX)
  
  -- xml datatype and its capabilities rock
  SELECT  @SourceDB = T.c.query('/AuditMsg/SourceDb').value('.[1]', 'sysname'),
          @SourceTable = T.c.query('/AuditMsg/SourceTable').value('.[1]', 'sysname'),
          @SourceIdent = T.c.query('/AuditMsg/SourceIdent').value('.[1]', 'uniqueidentifier'),
          @UserID = T.c.query('/AuditMsg/UserId').value('.[1]', 'NVARCHAR(50)'),
          @DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
          @ChangedData = T.c.query('*')
  FROM    @msgBody.nodes('/AuditMsg/ChangedData') T(c)
  
  INSERT INTO dbo.AVE_Stamm(SourceDB, SourceIdent, UserID, DMLType, ChangedData)
  SELECT @SourceDB, @SourceIdent, @UserID, @DMLType, @ChangedData
  </code></pre>
  
  <p>But when I change the <code>INSERT</code> statement to</p>
  
  <pre><code>SET @sql = 'INSERT INTO @SourceTable (SELECT @SourceDB, @SourceIdent, @DMLType, @ChangedData, @UserID)';
  EXEC (@sql);
  </code></pre>
  
  <p>it isn't working anymore. Can please someone help me, what's wrong here?</p>
  
  <p>I use this script in a stored procedure of a SQL Server 2008 R2.</p>