SQL Insert with a dynamic Column name

Question

I have a temp table that has all the Names of Column for now there are 4 of them and I am looping through the temp table and do a Insert into another table, Now the issue is that the Column name that I want to insert depends on what it gets from the loop:

Here is the code:

Declare @OutputTable table 
                     (
                         RowID int IDENTITY(1, 1),  
                         ClientID int, 
                         ClientName VarChar(100), 
                         ScoreModule1 VarChar(100), 
                         ScoreModule2 VarChar(100), 
                         ScoreModule3 VarChar(100), 
                         ScoreModule4 VarChar(100)
                     )

Declare @TempModuleNumber table 
                          ( 
                               RowIDNumber int IDENTITY(1, 1), 
                               ModuleNumber varchar (300)
                          )

INSERT INTO @TempModuleNumber(ModuleNumber) 
VALUES ('ScoreModule1'), ('ScoreModule2'),
       ('ScoreModule3'), ('ScoreModule4')

Declare @ModuleRowCountNumber int 
Declare @ModuleCounterNumber int 
Declare @ModuleNumber varchar(300) 

Select @ModuleRowCount = COUNT(@ModuleNumber) 
from @TempModuleNumber

set @ModuleCounterNumber = 1 

while @ModuleCounterNumber <= @ModuleRowCount
begin
    Select @ModuleNumber = ModuleNumber 
    from @TempModuleNumber 
    where RowIDNumber = @ModuleCounterNumber

    Insert into @OutputTable (ClientID, ClientName, @ModuleNumber) --This is where the problem is the @ModuleNumber is. I get a "syntax error". Is there another way of doing this to it depends on what it gets from the Loop?

    Set @ModuleCounterNumber = @ModuleCounterNumber + 1
END

Answer 1

I only made change to the necessary part:

while @ModuleCounterNumber <= @ModuleRowCount
begin
    Select @ModuleNumber = ModuleNumber 
    from @TempModuleNumber 
    where RowIDNumber = @ModuleCounterNumber

    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 
    '
    Insert into @OutputTable (ClientID, ClientName, ' + @ModuleNumber + ')
    SELECT A,B,C  //<----------- THIS IS THE MISSING PART I WAS TALKING ABOUT
    '
    EXEC(@SQL)

    Set @ModuleCounterNumber = @ModuleCounterNumber + 1
END