Insert/fill data into a temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert/fill data into a temp table

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 09:58:37

Hello,

Please help on this. I need to fill a temp table with data that is dynamically extracted from source table.

Source:
Select * from sourceTable where dtRange between 6/1/2007 and 6/3/2007

Normal output:
6/1/2007 (100 rows)
6/2/2007 (200 rows)
6/3/2007 (200 rows)

Goal:
6/1/2007 (100 rows)
6/2/2007 (200 rows)
6/3/2007 (200 rows)
6/4/2007 (100 rows)
6/5/2007 (200 rows)
6/6/2007 (200 rows)
6/7/2007 (100 rows)
...
6/30/2007 (200 rows)

Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-17 : 10:06:11

Which part(s) of the query are dynamic?

Jim

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 10:12:09

Jim thanks for the reply. The dynamic I am refering to is just the date range so it could change from 1 day or 7 days, etc. Hope that make sense.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 10:14:06

Just a quick note: Date range also be 6/5/2007 to 6/12/2007 and not necessarily from beginning of month.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-17 : 10:15:21

You can pass date range (min and max) as paramters of stored proc and form the query with parameters:


Create Proc dbo.test
(
@StartDate datetime, 
@EndDate datetime
)
as
Select * from sourceTable where dtRange between @StartDate and @EndDate
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-17 : 10:25:30

Then you could make a stored procedure with one or two parameters, depending on what you use as a startdate.
You may have to tweak this to suit your data and its formats

CREATE PROCEDURE procFillTheTable (@startDate dateTime ,@NumOfDays int)
AS
BEGIN
CREATE TABLE #temp <cols and type here>
INSERT INTO #temp

SELECT <cols>
FROM sourceTable
WHERE dtRange between @startDate and DATEADD(day,@NumOfDays,@startDate)

END

Jim

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 10:26:26

Harsh,

Thanks for your input but that's not the goal. I can pull the data within the date range but the goal is to insert the resultset into a temp table so if fills up a month's worth of data.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-17 : 10:40:25

So what you really want is dates as columns of the temp table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 10:48:06

No. Here's the before and after.

Query result:
RowID myDate Description
1 6/3/2007 something here
2 6/3/2007 something here
3 6/4/2007 something here
4 6/4/2007 something here
5 6/4/2007 something here
6 6/4/2007 something here
7 6/5/2007 something here
8 6/5/2007 something here
9 6/5/2007 something here
10 6/5/2007 something here

Goal output:
1 6/1/2007 something here
2 6/1/2007 something here
3 6/2/2007 something here
4 6/2/2007 something here
5 6/2/2007 something here
6 6/2/2007 something here
7 6/3/2007 something here
8 6/3/2007 something here
9 6/3/2007 something here
10 6/3/2007 something here
11 6/4/2007 something here
12 6/4/2007 something here
13 6/5/2007 something here
14 6/5/2007 something here
15 6/5/2007 something here
16 6/5/2007 something here
17 6/6/2007 something here
18 6/6/2007 something here
19 6/6/2007 something here
20 6/6/2007 something here
...
### 6/30/2007 something here
### 6/30/2007 something here

Same block of data from query result repeated filling up full June month.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-17 : 11:09:56

"Just a quick note: Date range also be 6/5/2007 to 6/12/2007 and not necessarily from beginning of month"
seems to contradict this
"Thanks for your input but that's not the goal. I can pull the data within the date range but the goal is to insert the resultset into a temp table so if fills up a month's worth of data."

Could you specify exactly what you're after? Do you want to pull the entire month that contains the date range?

Jim

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-17 : 11:29:10

Guys, sorry for the confusion. I figured out what I need for now. The basic idea is to multiple a query output so it fills up a whole month.

Here's what I have:


declare @LastDayMonth int
declare @ctr int
declare @dateCount int

set @ctr = 1
set @LastDayMonth = 30

while @ctr <= @LastDayMonth
begin
	INSERT INTO #resultTemp2
	SELECT *
	FROM #resultTemp
	set @ctr = @ctr + 1
end

#resultTemp has a set of data with date range 6/5/2007 to 6/10/2007
#resultTemp2 now has data filled month of June.

Thanks for all the feedback.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-17 : 11:40:19

#resultTemp2 will now have 30 copies of #resultTemp in it!

This will give you a complete months of data for the date inputted
INSERT INTO #temp
SELECT *
from yourtable
where date >= DATEADD(month,DATEDIFF(month,0,@Date),0)
and date < DATEADD(month,DATEDIFF(month,0,@Date)+1,0)

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-18 : 13:52:27

Figured it out:


declare @StartDate datetime
declare @StartDate datetime
declare @tempDate datetime
declare @counter int
declare @counter2 int
declare @LastDayMonth

set @StartDate = CONVERT(DATETIME, '2007-06-15 00:00:00', 102)
set @EndDate = CONVERT(DATETIME, '2007-06-20 00:00:00', 102)
set @tempDate = @StartDate
set @counter = 1
set @counter2 = 1
set @LastDayMonth(getDate())

while (@counter <= DAY(@LastDayMonth))
begin

	if @tempDate > @EndDate
	begin
		set @counter2 = 1	
		set @tempDate = DATEADD(d, @counter2-1, @StartDate)
	end

		SET @InsertStatement = 
			'
				INSERT stuff here

			'

		execute (@InsertStatement)		

		set @tempDate = DATEADD(d, @counter2, @StartDate)
		set @counter2 = @counter2 + 1
		set @counter = @counter + 1
end

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-09-19 : 03:32:27

select * into NewTable from OldTable
or
select * into #NewTable from OldTable

select * from NewTable
select * from #NewTable

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-19 : 07:38:12

kiruthika, sorry I meant "I figured it out" not someone should figure it out. I finally got what I was looking for thanks to the awesome help of the people here. This is the best forum I've been yet. Very helpful and quick responses.

THANKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources