Divide a main table into multiple tables using SQL server -

hi trying create multiple tables out of main table in sql server. e.g

the main table looks like

a 1 2 3 b 4 b 5 b 6 

the output should :

table a:

a 1 2 3 

table b:

b 4  b 5  b 6 

the main table updated every week can have different alphabets. want create dynamic query automatically divide main table 'n' different tables depending on how many different n's there , name table based on nth value.

yes achievable, curse , blessing dynamic sql erland sommarskog

create table @tbl

the desire here create table of name determined @ run-time.

if @ arguments against using dynamic sql in stored procedures, few of them applicable here. if stored procedure has static create table in it, user runs procedure must have permissions create tables, dynamic sql not change anything. plan caching has nothing it. etc.

nevertheless: why? why want this? if creating tables on fly in application, have missed fundamentals database design. in relational database, set of tables , columns supposed constant. may change installation of new versions, not during run-time.

sometimes when people doing this, appears want construct unique names temporary tables. unnecessary, built-in feature in sql server. if say:

create table #nisse (a int not null)

then actual name behind scenes longer, , no other connections able see instance of #nisse.

if want create permanent table unique user, don't want stay connected , therefore cannot use temp tables, may better create 1 table clients can share, first column key private client. discuss method little more closely in article how 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 return (     select id, value     tablename     id = @parameter1 )  -- usage example select * dbo.fxnexample('a')   -- data 'a' select * dbo.fxnexample('b')   -- data 'b' 

edit

you can use view , pass them users. if still want tables feel free change code, should idea. why views, because table still 1 , dynamics view can mimic multiple tables. when data updated in main table views immediately, no need update/insert.

sqlfiddledemo

create table main_tab(suffix nvarchar(10) not null, val int);  insert main_tab(suffix, val) values ('a', 1), ('a', 2), ('a', 3),        ('b', 4), ('b', 5), ('b', 6),        ('c', 7), ('c', 8), ('c', 9);   /* list of suffixes */ select suffix,       [row_id] = row_number() over(order suffix) #temp main_tab group suffix;  declare @name_suffix nvarchar(100),         @sql nvarchar(max),         @view_name nvarchar(max),         @index int = 1,         @total int = (select count(*) #temp);  /* used simple while loop can change cursor if needed */ while (@index <= @total) begin     select @name_suffix = suffix     #temp     row_id = @index;     select @sql =            n'create view [dbo].[view@name_suffix]                     select              t.suffix,              t.val           [dbo].[main_tab] t           t.suffix = ''@name_suffix''           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 1 */    /* instead of exec can use exec [dbo].[sp_executesql] , pass params explicitly */    if object_id(@view_name, 'v') null                    exec(@sql)     set @index += 1; end              /* check if can query views */ select * viewa;  select * viewb;  select * viewc;