0

I have a SQL Server select statement which is inserting data in a created table.

Right now I have to define all parameters and run the statement for each of them. To be more efficient I would like to run the statement like multiple times for a row of some consecutive days, e.g. for all days between 2013-31-12 and 2014-02-19.

At the end I want to have the resulting data in one table with different timestamps in the defined timestamp column (let's say the defined variable).

Right now I working with this:

DECLARE @historicalDate DATETIME

SELECT  @historicalDate = '2013-31-12'#

SELECT * ....
rest...
into #data

I know how to do it in C or MATLAB. I would just use a for loop and define a running variable to run the statement and store it in a table.

But how to handle in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3165675
  • 45
  • 7

3 Answers3

0

You can run While loops in SQL. Check out http://technet.microsoft.com/en-us/library/ms178642.aspx for syntax.

So for your case, something like:

WHILE (SELECT datetime FROM historicalDate) < '2014-02-19')
{
   //your code to update datebase
}
suhMAN
  • 179
  • 1
  • 4
  • 13
  • Don't use WHILE loops in SQL Server! SQL Server is a **set-based, relational** system - use **sets of data** and don't fall back into RBAR (row-by-agonizing-row) procedural processing ..... – marc_s Feb 19 '14 at 15:10
  • This is something I have heard a lot of time. But how to run the statement for sets of data? It is possible to make an array of dates? And then use this array as input? – user3165675 Feb 19 '14 at 15:13
  • @user3165675: anything "array" in SQL Server is a **table** - that's how to hold multiple rows of data (real, temporary, table variable) – marc_s Feb 19 '14 at 15:27
0

If you want to insert all rows for a defined date range - use something like this:

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromDate = '20131231'   -- use the robust ISO-8601 format here to avoid issues!
SET @ToDate = '20140219'

SELECT (list of columns)
INTO #data(list of columns)
WHERE TimestampColumn >= @FromDate 
  AND TimestampColumn <= @ToDate

This does your whole processing in a single, set-based operation - nice and easy

Or what exactly is it that you're trying to achieve if this doesn't solve your problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I think this will not work in my case. I have to "backtest" something for the past. So I have to write an code that is trying to catch the as-is status for each day in the past. Propably I really have to use loops... – user3165675 Feb 19 '14 at 15:21
  • 1
    You can still do `CASE` and `IF` statements in SQL as well. Loops are a last resort for the most part. – TTeeple Feb 19 '14 at 15:27
0

The simplest solution is to use a calendar table and join the calendar table for the specified date range to your query. If you haven't got a calendar table see: How to create a Calender table for 100 years in Sql

You would then be able to do something along the lines of:

Select <fields>
FROM <YourTable> T
CROSS JOIN CALENDAR C
WHERE C.DATE BETWEEN @startdate and @enddate
Community
  • 1
  • 1
Dave Bennett
  • 788
  • 1
  • 7
  • 13