Divide a main table into multiple tables using SQL

Question

I am trying to create multiple tables out of a main table in SQL server. e.g.:

The main table looks like

A 1
A 2
A 3
B 4
B 5
B 6

The output should look like :

Table A:

A 1
A 2
A 3

Table B:

B 4 
B 5 
B 6

The main table is updated every week so can have different alphabets. So I want to create a dynamic query that will automatically divide the main table into 'n' different tables depending on how many different n's are there and also name the table based on the n^th value.

Answer 1

Yes it is achievable, but Curse and Blessing Dynamic SQL by Erland Sommarskog

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined at run-time.

If we just look at the arguments against using dynamic SQL in stored procedures, few of them are really applicable here. If a stored procedure has a static CREATE TABLE in it, the user who runs the procedure must have permissions to create tables, so dynamic SQL will not change anything. Plan caching obviously has nothing to do with it. Etc.

Nevertheless: Why? Why would you want to do this? If you are creating tables on the fly in your application, you have missed some fundamentals about database design. In a relational database, the set of tables and columns are supposed to be constant. They may change with the installation of new versions, but not during run-time.

Sometimes when people are doing this, it appears that they want to construct unique names for temporary tables. This is completely unnecessary, as this is a built-in feature in SQL Server. If you say:

CREATE TABLE #nisse (a int NOT NULL)

then the actual name behind the scenes will be something much longer, and no other connections will be able to see this instance of #nisse.

If you want to create a permanent table which is unique to a user, but you don't want to stay connected and therefore cannot use temp tables, it may be better to create one table that all clients can share, but where the first column is a key which is private to the client. I discuss this method a little more closely in my article How to Share Data between Stored Procedures.

Possible solution using Inline Parametrized Table-Valued Function (you can use Stored Procedure if needed):

CREATE FUNCTION dbo.fxnExample (@Parameter1 NVARCHAR(1))
RETURNS TABLE
AS
RETURN
(
    SELECT id, value
    FROM TableName
    WHERE id = @Parameter1
)

-- Usage Example
SELECT * FROM dbo.fxnExample('A')   -- only data from 'A'
SELECT * FROM dbo.fxnExample('B')   -- only data from 'B'

EDIT

You can use view for this and pass them to users. If you still want tables feel free to change code, you should get the idea. Why views, because table is still one and you get dynamics VIEW that can mimic your multiple tables. Also when data will be updated in main table your all views will get it immediately, no need to update/insert.

SqlFiddleDemo

DBFiddle Demo (updated)

CREATE TABLE main_tab(suffix NVARCHAR(10) NOT NULL, val INT);

INSERT INTO main_tab(suffix, val)
VALUES ('A', 1), ('A', 2), ('A', 3),
       ('B', 4), ('B', 5), ('B', 6),
       ('C', 7), ('C', 8), ('C', 9);


/* Get list of suffixes */
SELECT suffix,
      [row_id] = ROW_NUMBER() OVER(ORDER BY suffix)
INTO #temp
FROM main_tab
GROUP BY suffix;

DECLARE @name_suffix NVARCHAR(100),
        @sql NVARCHAR(MAX),
        @view_name NVARCHAR(MAX),
        @index INT = 1,
        @total INT = (SELECT COUNT(*) FROM #temp);

/* I used simple while loop but you can change to CURSOR if needed */
WHILE (@index <= @total)
BEGIN

   SELECT @name_suffix = suffix 
   FROM #temp 
   WHERE row_id = @index;

   SELECT @sql = 
          N'CREATE VIEW [dbo].[View@name_suffix]
          AS
          SELECT
             t.suffix,
             t.val
          FROM [dbo].[main_tab] t
          WHERE t.suffix = ''@name_suffix''
          WITH CHECK OPTION'

   SELECT 
        @view_name = REPLACE('[dbo].[View@name]', '@name', @name_suffix)
       ,@sql = REPLACE(@sql, '@name_suffix', @name_suffix)

   /* Check if view exists, if not create one */
   /* Instead of EXEC you can use EXEC [dbo].[sp_executesql]
       and pass params explicitly */
   IF OBJECT_ID(@view_name, 'V') IS NULL            
       EXEC(@sql)
     
   SET @index += 1;
END            

/* Check if you can query views */
SELECT *
FROM ViewA;

SELECT *
FROM ViewB;

SELECT *
FROM ViewC;