- Home /

I want to insert data in a table automatically. i want to create automatic data wherein there are 4 columns- fromvalue, tovalue, points, pointvalue. the result should be a continous data like from 10 to 19.99, points is 1. from 20 to 29.99 the points is 2 & so on. I want to enter the data in the table upto 1200 points. The pointvalue is 0 for all entries.

Comment

Best Answer

**Answer** by iainrobertson ·

Try using a tally (or numbers) table: -- create a tally table ; with e1(n) as ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 ) , e2(n) as (select 1 from e1 a, e1 b) , e4(n) as (select 1 from e2 a, e2 b) , e8(n) as (select 1 from e4 a, e4 b) , tally as (select top 100000 n = row_number() over (order by (select null)) from e8) select n = isnull(n,0) into #tally from tally alter table #tally add constraint pk_tally primary key (n) -- create a set of outputs using calculations based on the tally number select top 1200 from_value = n , to_value = cast(n as decimal(10,2)) - 0.01 + 10 , points_value = n / 10 from #Tally where n % 10 = 0 order by n Edit: forgot the order by for the select, which is important

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges