Solved

insert rows into temp table

Posted on 2013-01-14
6
411 Views
Last Modified: 2013-01-15
im trying to insert rows into a temp table but am not having any luck.


declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'
set @today = getdate()
declare @myCounter as int
set @myCounter = 0

--drop table #tempDates

CREATE TABLE #tempDates(myDate date NULL)

if(@today != @StartDate)
begin
      --when today doesn't equal to startdate
      --ex: today 1/14 and start date 1/20
      -- should be 1/20, 1/21, 1/22, 1/23, 1/24, 1/25, 1/27
      while (@myCounter <= 7)
      begin
            set @StartDate = dateadd(dd, 1, @StartDate)
            insert into #tempDates values (@StartDate)
      end
end
else
begin
      --when today does equal startdate
      --ex: today 1/14 and start date 1/14
      -- should be 1/14, 1/15, 1/16, 1/17, 1/18, 1/19, 1/20
      while (@myCounter <= 7)
      begin
            set @StartDate = dateadd(dd, 1, @StartDate)
            insert into #tempDates values (@StartDate)
      end
end

select * from #tempDates
0
Comment
Question by:fwstealer
  • 3
  • 3
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38776346
Works fine for me, atlhough of course the dates start at 1/21 since the day is added before it goes to the temp table.
0
 

Author Comment

by:fwstealer
ID: 38776397
i can't get it to work at all; need the dates to begin at the startdate so how is that corrected.

when i run it - it just spins and never returns anything
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38776596
Oh, I corrected the no-return part: sorry, based on your comments about the results returned, I assumed you had to.  Here it is:


declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'
set @today = getdate()
declare @myCounter as int
set @myCounter = 0

--drop table #tempDates

CREATE TABLE #tempDates(myDate date NULL)

if(@today != @StartDate)
begin
      --when today doesn't equal to startdate
      --ex: today 1/14 and start date 1/20
      -- should be 1/20, 1/21, 1/22, 1/23, 1/24, 1/25, 1/27
      while (@myCounter <= 7)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1  
      end
end
else
begin
      --when today does equal startdate
      --ex: today 1/14 and start date 1/14
      -- should be 1/14, 1/15, 1/16, 1/17, 1/18, 1/19, 1/20
      while (@myCounter <= 7)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1
      end
end

select * from #tempDates
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:fwstealer
ID: 38778229
just noticed that the solution provided is producing 8 rows when i only need 7; what needs to change to get 7 rows?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38778774
Change:

while (@myCounter <= 7)

to

while (@myCounter < 7)

in both places.
0
 

Author Comment

by:fwstealer
ID: 38778787
thanks scott
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now