-1

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
Thom A
  • 88,727
  • 11
  • 45
  • 75
Bunny
  • 43
  • 1
  • 10
  • 3
    No you cannot do that; you are trying to dynamically generate a table hence you'd need *dynamic SQL*. But don't do this, it's an anti-pattern at best. If you want to encapsulate data to export then create a view. Also note an order by expression here is redundant and see [bad habbits to kick: using-table-aliases-like-a-b-c](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Stu Jan 07 '23 at 19:58
  • 2
    [Limitations and Restrictions](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver16): You cannot specify a table variable or table-valued parameter as the new table. – Adam Silenko Jan 07 '23 at 20:02
  • 2
    SQL isn't a programming language, so trying to write a "SQL Program" isn't ever going to be effecient. It excels at set based processes, which programs often are awful at. Conversely it's terrible at iterative tasks that programs often are good at. You have an iterative task here, so it's not going to be "quick". Programming language and query languages are *very* different. – Thom A Jan 07 '23 at 20:22
  • @Stu, thanks. does that mean I declare varchar variable to contain the whole code as string in quotations? And then execute that variable using EXEC(@sqlstring)? – Bunny Jan 07 '23 at 20:24
  • @Larnu, thanks. I am not an IT person. So didn’t understand your answer clearly. What I sense is that SQL will always be slower in running while loop codes. – Bunny Jan 07 '23 at 20:31
  • 1
    @Bunny Yes, in a nutshell, but using dynamic SQL effectively requires a particular mindset and tbh is not for a beginner or someone not well experienced with the platform; although still note this is not a good idea and better options exist. – Stu Jan 07 '23 at 20:31

1 Answers1

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
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • different tables are required since they are pulled into some system for other teams. It’s not my choice really to decide table format. – Bunny Jan 08 '23 at 03:38
  • Is the requirement really about creating separate "tables" for each employee - or do you actually need to create separate "files" to be sent to the other teams? – Jeff Jan 08 '23 at 17:24