title: Using dynamic sql to automate inserts in different tables
body: <p>I am trying to automate multiple inserts into multiple tables.</p>
  <p>Basically, I have one temp table, with Customer codes and I added a row number. I also have a source tables, which contains the customer codes and personal info. Now I would like to insert alle the Person ID's from customer 1 into #1, all the PersonID's from customer 2 into #2 and so on.</p>
  <p>I used this code, but it generates an error:</p>
  <pre><code>DECLARE @Customer_Code INT
  DECLARE @Row INT = 1
  DECLARE @SQL NVARCHAR (MAX)
  
  
  WHILE @Row = SELECT MAX(Rij) FROM #M
  
  BEGIN 
      SELECT @Customer_Code = Customer_Code FROM #M WHERE RowNr = @Row
  
      SET @SQL = 
  
      'SELECT PersonID 
      INTO #@Customer_Code
      FROM T_Sourcetable 
      WHERE YEAR (Date) = 2016 
        AND Customer_Code = @Customer_Code'
      
      EXECUTE (@SQL)
         
      SET @Row = @Row + 1
      SET @SQL = ''
  END
  </code></pre>
  <p>Could anyone please help me here. Thanks in advance.</p>
  <p>PS. I'm using MS SQL Server 2008.</p>
  <p>Sample data:</p>
  <h1>M: Customer_Code AND RowNumber</h1>
  <p>1,1
  2,2
  3,3
  4,4</p>
  <p>In reality, obviously Customer code != RowNumber.</p>
  <p>Source Table: COLUMNS (Customer_Code    PersonID)</p>
  <pre><code>1, 8 ,
  1, 9 ,
  1, 10,
  1, 11,
  2, 9 ,
  2, 12,
  2, 13,
  2, 14,
  3, 8 ,
  3, 14,
  3, 15,
  3, 17,
  4, 8 ,
  4, 10,
  4, 12,
  4, 14
  </code></pre>
  <p>Result should be:</p>
  <pre><code>Table #1: 
  PersonID
  8
  9
  10
  11
  
  Table #2
  PersonID
  9
  12
  13
  14
  
  Table #3
  PersonID
  8
  14
  15
  17
  
  Table #4
  PersonID
  8
  10
  12
  14
  </code></pre>