Stored procedure to create tables for each employee code using INTO clause

Question

I am trying to create tables which are specific to employee ID.

I can run simple select statement in a stored procedure. But I want to create tables using INTO clause which I can then export into CSV files. When I write INTO clause, I get an error "Incorrect syntax near 'zz_'". If I hover mouse over 'zz_', I see error as "expecting '.', ID, or Quoted_ID".

I even tried to replace it using a variable called @table_employee (not included in below code). But even that code doesn't work. I do not want to use INSERT INTO way of writing query.

Also is below code an efficient way of writing SQL program?

DECLARE @employeeID INT
DECLARE @deptartment VARCHAR(2)

SET @employeeID = (select MIN(ID) from employee)
SET @deptartment = 'HR'

WHILE @employeeID <= (SELECT MAX(ID) FROM employee)

BEGIN

    IF EXISTS 
    (
    select * from companydata
    where ID = @employeeID
    )

        select  a.employeeID, b.Name, 
            SUM(a.RATE * a.Quantity) 'Revenue'
        into 'zz_' + Region + '_' + Product + '_' + @employeeID
        from
            employee a join
            companydata b on a.employeeID = b.ID
        where a.employeeID = @employeeID and a.departmentname = @deptartment
        group by a.employeeID, b.Name, Region, Product
        order by a.employeeID, b.Name

    ELSE
        PRINT CAST(@employeeID as varchar) + ' Does not exist'

    SET @employeeID = @employeeID + 1

END

Answer 1

You don't really want a bunch of different tables for this. A single summary table can efficiently hold the data for all employees, eg

select  
    a.employeeID, 
    b.Name, 
    Region,
    Product 
    SUM(a.RATE * a.Quantity) 'Revenue'
into zz_HR_RevenueByEmployeeRegionProduct
from
    employee a join
    companydata b on a.employeeID = b.ID
where a.departmentname = 'HR'
group by a.employeeID, b.Name, Region, Product