How to create database table dynamically and insert data selected by query

Question

I'm working on website where I need to find rank of user on the basis of score. Earlier I'm calculating the score and rank of user by sql query .

select * from (
    select
        usrid,
        ROW_NUMBER()
        OVER(ORDER BY (count(*)+sum(sup)+sum(opp)+sum(visited)*0.3) DESC) AS rank,
        (count(*)+sum(sup)+sum(opp)+sum(visited)*0.3 ) As score
    from [DB_].[dbo].[dsas]
    group by usrid) as cash
where usrid=@userid

Please don't concentrate more on query because this is only to explain how I select data.

Problem: Now I can't use above query because every time I use rank it need to select rank from dsas table and data of dsas table is increasing day by day and slows down my website.

What I need is select data by above query and insert in another table named as score. Can we do anything like this?

dan1111 · Accepted Answer · 2012-12-14 12:21:23Z

2

A better solution is to either include score as a field in your user table or have a separate table for scores. Any time you add new sup, opp, or visited data for a user, also recalculate their score at that time.

Then to get the highest ranking users, you will be able to perform a very simple select statement, ordering by score descending, and only fetching the number of rows you want. It will be very fast.

answered Dec 14, 2012 at 12:21

dan1111

6,5662 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chetan Saini Over a year ago

yeah i m thinking about that only . My problem is that how to insert data dynamically of 1000 user's selected by above query .

dan1111 Over a year ago

@chetanSaini, you could add an extra query to update score at each location that changes sup,opp, and visited. Or you could add a trigger to the database that will do that automatically.

chetan Saini Over a year ago

first we have to create table with fields usrid and score . After that insert all user data then we have to take step suggested by you. So our first priority is to create table with all user data .

Collectives™ on Stack Overflow

How to create database table dynamically and insert data selected by query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related