Create a new table from an existing table for each distinct value of a particular column and name the new table with this distinct value

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

Here's the depiction of the existing table -

Existing_Table		
Column_1	Column_2	Column_3
A	        XYZ	        Group1
B	        ABC	        Group2
C	        CBA	        Group1
D	        PQR	        Group3
E	        XXX	        Group2
F	        ABC123	        Group1

So the unique values for which I will need to create a new table will be -

UNIQUE Column_3
Group1
Group2
Group3

Now the newly created tables expected to look like-

New_Table_Group1	
Column_1	Column_2
A	        XYZ
C	        CBA
F	        ABC123

New_Table_Group2	
Column_1	Column_2
B	        ABC
E	        XXX

New_Table_Group3	
Column_1	Column_2
D	        PQR

Here the name of the table will be based on the unique value based on which the table is created.

Couldn't achieve this. Can someone please help achieve the expected result? Any help is much appreciated.

What I have tried:

I have tried the below code-

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
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 Column_3 group value
        SET @i = @i + 1
    END

This fails because this only creates a single new table by name 'New_Table_@Group' itself and doesn't substitute the expected unique value from the intended column and hence doesn't create new table for each unique value of the intended column.

Posted 20-Jun-18 0:07am

shreessmrv

Updated 26-Jun-18 3:01am

Add a Solution

Comments

CHill60 20-Jun-18 6:09am

Why would you want to do this?

shreessmrv 20-Jun-18 6:22am

That's a requirement for something that I'm working on. These new tables will be used by different applications for further processing.

CHill60 20-Jun-18 7:50am

There are at least two better approaches
1. Make the other applications access the original table, OR (better)
2. Create Views that filter the table based on group.

It is very bad practice to create duplicate information like this

shreessmrv 22-Jun-18 2:54am

Agreed. But this is not feasible in my scenario. the application needs the input in a particular format with predefined set of fields only. The base table has more details hence the application doesn't accept it and thus fails to do the task expected. Only after checking on all these we've come up on this plan of creating different exclusive tables.

CHill60 22-Jun-18 6:34am

You've just described the exact reason for having views (it also contradicts how you described your problem). Everything you are describing can be achieved using Views without duplicating the data.
To the calling application there should be no apparent distinction between querying a view and querying a table.
If the application doesn't "accept" the results of a query against a table (or a view) then there is a problem with the application. For example it is possibly using "SELECT * FROM ..." - this is very bad practice. List the columns that are required.

shreessmrv 25-Jun-18 3:21am

ACHill60 Ahh! There's a problem. You've been telling about the cons. of creating multiple tables and rather choose views. Okay, I've understood that! But I do not have a problem in creating a table or a view, as such. My problem is actually with CREATING one dynamically for each existing and the future values of a particular column. So I have been responding in a different view point!
The column values based on which I need a tables or views to be created will get its values dynamically. So no matter whether I create tables or a Views, I want to know how to create it, and yes, this should keep happening for any future values that the column would get. If you can help me achieve this, that'll be more helpful. :)

Herman<T>.Instance 20-Jun-18 7:03am

put [] around @Group It is a Variable
Better:
DECLARE @TableName NVARCHAR(200)
SET @TableName = 'dbo.New_Table_'+ [@Group]

IF OBJECT_ID([@TableName], 'U') IS NOT NULL
DROP TABLE [@TableName]

Herman<T>.Instance 20-Jun-18 7:04am

What should this part do?
select Column_1, Column_2
into New_Table_@Group
from Existing_Table where Column_3 = @Group;

trying to put data in a table without any columns?

shreessmrv 20-Jun-18 9:31am

This part is supposed to create the new tables (the select-into approach of creating tables using columns from an existing table).
This will create new table for each unique value of the Column_3 by naming the tables in 'New_Table_[@Group]' format with the columns Column_1, Column_2 where Column_3= [@Group]

Herman<T>.Instance 21-Jun-18 2:24am

Does it work now?

shreessmrv 21-Jun-18 7:31am

Unfortunately nope! This is giving me an 'Invalid Column Name' error -

Msg 207, Level 16, State 1, Procedure sp_Tables_For_Group, Line 25
Invalid column name '@Group'.
Msg 207, Level 16, State 1, Procedure sp_Tables_For_Group, Line 26
Invalid column name '@TableName'.

Herman<T>.Instance 22-Jun-18 5:23am

see the quotes in my example:
DECLARE @TableName NVARCHAR(200)
SET @TableName = 'dbo.New_Table_'+ [@Group]

shreessmrv 25-Jun-18 3:30am

Yes, I have used it exactly as you've suggested. I haven't missed the quotes.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-06-25T01:44:00

You need to use dynamic sql in order to insert the correct group name into the table name(s).
E.g (untested)

SQL

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
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 #test		-- sub your table name here

-- 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);

		declare @sql nvarchar(max)
		declare @drop nvarchar(max)

		set @drop = 'IF OBJECT_ID(''dbo.New_Table_' + @Group + ''', ''U'') IS NOT NULL DROP TABLE dbo.New_Table_' + @Group
		EXECUTE sp_executesql  @drop

		set @sql = 'SELECT Column_1, Column_2 INTO New_Table_' + @Group + ' FROM #test WHERE Column_3 = ''' + @Group + ''''
		EXECUTE sp_executesql  @sql

        -- increment counter for next Column_3 group value
        SET @i = @i + 1
    END

Also note the use of square brackets around the column name Group in table @temp_table (and everywhere it is used). This is because Group is a reserved word and should not be used as a column name. It can be used as a column name only if surrounded by square brackets - [Group]

[EDIT] For the benefit of other readers, please review my comments to the OP. A better approach would be to create Views to be used by the other application. are describing can be achieved using Views without duplicating the data. To the calling application there should be no apparent distinction between querying a view and querying a table.
The solution provided above could be altered to create views rather than tables, the key is that the sql statements must be dynamic.

shreessmrv · Accepted Answer · 2018-06-26T03:03:00

I got this working with a cursor.

Here's the code that I'm using-

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

Thanks to @digimanus and @CHill60 for helping out on this that got me learn some good stuff around this.

Create a new table from an existing table for each distinct value of a particular column and name the new table with this distinct value

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0