x

Loop Day Wise SQL to Insert Data

I have a Query: SELECT * FROM tble WHERE BILL_DATE BETWEEN '01-NOV-16' AND '01-NOV-16'

Now i need to know if bellow thing can be arranged

For i=1 to 30 Loop SELECT * FROM tble WHERE date BETWEEN 'i-NOV-16' AND 'i-NOV-16'

Insert Data in a table [SQL Server]

Next i

Thanks, Sami

more ▼

asked Dec 19, 2016 at 05:01 AM in Default

avatar image

samihuq
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Step back and think about the data in a complete set rather than 30 loops.

You have a query that retrieves one day's worth of data, and you want a month's worth, so you are attempting to reuse the 'day' query for the number of days in the month. Don't do it. Write the query to retrieve the data you want, for example

 select * from tble where date between '01-nov-16' and '30-nov-16'

OK. But you might say that you don't always want a complete month. You might want 20 days worth of data starting from some given start date! Again, describe the result set....

 declare @startdate date
 set @startdate = '19 October 2016'

 select * from tble where date between @startdate and dateadd(day, 20, @startdate)


And from this you could even make the 'number of days' a parameter.....

But always describe the result set you want SQL to return, rather than describing how to get the results.

more ▼

answered Dec 19, 2016 at 08:57 AM

avatar image

Kev Riley ♦♦
66.2k 48 63 81

Thanks Man, Well the problem is. i do not have the liberty to query data more then 1 days, because one day worth data returns 5 Million or rows even after grouping, and i need to insert that 5 million of rows to a different table and this needs to be done for 180 days [6 Month].

Querying on Multiple Date slows down the query since indexing is being done on date column.

Dec 19, 2016 at 11:09 AM samihuq
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1155
x441
x247
x146
x31

asked: Dec 19, 2016 at 05:01 AM

Seen: 138 times

Last Updated: Dec 19, 2016 at 11:09 AM

Copyright 2017 Redgate Software. Privacy Policy