Run a Select-into command to create a new table for each UNIQUE value of a particular column

Question

I have a table on SQL Server database from which I need to create different tables for each unique value of a particular column.

I tried below code:

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar
DECLARE @temp_table TABLE (
    idx smallint Primary Key IDENTITY(1,1), Group varchar(200)
    )

-- populate group table
INSERT @temp_table
SELECT distinct Column_3 FROM Existing_Table

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table) 
IF @numrows > 0
    WHILE (@i <= @indexcount)
    BEGIN
      -- get the next Group primary key
        SET @Group = (SELECT Group FROM @temp_table WHERE idx = @i);
        IF OBJECT_ID('dbo.New_Table_@Group', 'U') IS NOT NULL 
            DROP TABLE dbo.New_Table_@Group;
        select Column_1, Column_2  
        into New_Table_@Group
        from Existing_Table where Column_3 = @Group;
        -- increment counter for next employee
        SET @i = @i + 1
    END

This is executing and without prompting any error, but is also failing to give the expected result. It does not create any new table. While executing it shows that so-and-so number of rows affected but when I check the list of tables, no such new table will be created.

Can someone please help achieve the expected result? Any help is much appreciated.

depiction of existing table and new tables to be created - Here is a depiction of the table that I have and the intended resultant table to be achieved.

Your code snippet will create a single new table named `dbo.New_Table_@Group` but drop and recreate it each iteration. You need to use dynamic SQL for your task. See http://www.sommarskog.se/dynamic_sql.html. — Dan Guzman, Jun 19 '18 at 11:55
@DanGuzman I looked into the link that you suggested but I couldn't make out much as this is very much new to me. I got to know about when to not use the way that I have used variables but couldn't find(or I did not understand) about how to use it in a scenario similar to that of mine. — Shreesha, Jun 21 '18 at 12:40

score 0 · Answer 1 · answered Jun 22 '18 at 10:38

T-SQL syntax does not allow one to parameterize object names so you need to use dynamic SQL for this task.

Below is a script that shows how to build and execute a dynamic script to (re)create a table for each group. I used a cursor instead of a pseudo cursor (loop) and table variable in this example since the loop provides no value over a cursor in this case; both are RBAR methods.

Note that this dynamic SQL method is not something to be done routinely as it may be an indicator of a flawed application design. Here, it seems there should be a single table for all groups rather separate tables because they appear the be the same entity. See Erland Sommarskog's the Curse and Blessings of Dynamic SQL article for a thorough discussion on the subject.

DECLARE @Group varchar(200);
DECLARE @CreateTableScript nvarchar(MAX);

DECLARE groups CURSOR LOCAL FAST_FORWARD FOR
    SELECT
          Column_3 AS [Group]
        , N'IF OBJECT_ID(N''dbo.' + QUOTENAME('New_Table_' + Column_3) + ''', ''U'') IS NOT NULL 
                DROP TABLE dbo.' + QUOTENAME('New_Table_' + Column_3) + ';
            select Column_1, Column_2  
            into dbo.' + QUOTENAME('New_Table_' + Column_3) + '
            from Existing_Table where Column_3 = @Group;' AS CreateTableScript
    FROM (SELECT DISTINCT Column_3 FROM Existing_Table) AS groups;

OPEN groups;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM groups INTO @Group, @CreateTableScript;
    IF @@FETCH_STATUS = -1 BREAK;
    PRINT @CreateTableScript;
    EXEC sp_executesql
          @CreateTableScript
        , N'@Group varchar(200)'
        , @Group = @Group;
END;
CLOSE groups;
DEALLOCATE groups;
GO

Thank you very much, Dan! Now this works exactly the way I wanted it to. Thanks again. — Shreesha, Jun 26 '18 at 12:28
Dan, lately I observed that this is not creating tables for all the values in one go. I have placed this inside a stored procedure and calling this stored procedure from an application on page load. So it seems I will have to load the page 2-3 time to have the tables created. even after that, some times, I notice that tables 1 or 2 values still be missed or be created with no values. Why would this be happening? How to fix this? — Shreesha, Jun 29 '18 at 13:19
@Shreesha, I tested the T-SQL code and it works as expected from SSMS. Remove the `PRINT` statement (which is only for debugging) and add `SET NOCOUNT ON` in your stored procedure if you haven't already done so. That will avoid the DONE_IN_PROC (rowcount) messages from confusing applications that don't process all the result sets returned and prevent the proc from running to completion. — Dan Guzman, Jul 13 '18 at 10:32

Run a Select-into command to create a new table for each UNIQUE value of a particular column

1 Answers1