question

rahuldacool avatar image
rahuldacool asked

i want to insert values into table automatically in synchronised manner

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.
sqlserverautomation
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
iainrobertson avatar image
iainrobertson answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.