SQL SERVER: data insert

Question

Sorry guys! I just dont know what to do with this task. The data is the same as in this question here, but what to do in case when I insert new data in big_table.bench_id and i want this data be visible also in BATCH_ID table? I have tried to bound them with keys, but big_table already has main key so dont know what to do. Please any advice will be appreciated.

Big_table.bench_id:

**bench_id**
31
51
51
61
61
61
71
71

I have created another BATCH_ID table with two columns:

**distinct_bench**           **number**
-----------------------------

31                               1
51                               2
61                               3
71                               2

So for example, if i will add new code to the big_table.bench_id like '111':

**bench_id**
    31
    51
    51
    61
    61
    61
    71
    71
    111

so it will also appears in another table:

**distinct_bench**           **number**
    -----------------------------

    31                               1
    51                               2
    61                               3
    71                               2
    111                              1

Answer 1

Instead of creating a table for that purpose, you can create a view that will return you the desired information. For example, try following:

CREATE VIEW vwBigTable
AS
    SELECT bench_id AS [**distinct_bench**], COUNT(*) AS [**number**]
    FROM big_table
    GROUP BY bench_id

And then:

SELECT * FROM vwBigTable

Answer 2

Do you really need another table? You can create a view to achieve that.

create table xxTemp (bench_id int) ;

insert into xxTemp (bench_id) 
values (31)
    ,(51)
    ,(51)
    ,(61)
    ,(61)
    ,(61)
    ,(71)
    ,(71) ;

create view xxTempCount as 
Select  bench_id
    , COUNT(1) number 
From    xxTemp
Group By bench_id ;

select *
from xxTempCount ;

insert into xxTemp (bench_id) 
values (111) ;

select *
from xxTempCount ;

Elmer