insert total number of recs - SQL Server Forums

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

SQL Server Development (2000)

insert total number of recs

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-01-15 : 16:26:46

--I have times associated with a total column.
--I want to split them into one table

set nocount on
create table #default (gtime varchar(20), gnumber int)
insert into #default values('08:00:00', 3)
insert into #default values('09:00:00', 1)
insert into #default values('11:00:00', 2)
insert into #default values('13:00:00', 1)

--these are my default times i want to allow every day of the week
--as the user comes in the records will be created and placed into
--a scheduling table

create table #regulartimes (times)

--i want all records from #default into #times
--there should be 7 records in #times when done
--with insert
--any help would be appreciated

drop table #default
drop table #times

slow down to move faster...

izaltsman
A custom title

1139 Posts

Posted - 2002-01-15 : 17:00:22

Try this:


insert into #regulartimes
SELECT #default.gtime FROM #default INNER JOIN (
select ones.one+tens.ten as seqno
from (
select 0 as one union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all 
select 7 union all
select 8 union all
select 9 ) as ones 
cross join (
select 0 as ten union all
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all 
select 70 union all
select 80 union all
select 90 ) as tens ) as seq
ON #default.gnumber > seq.seqno

You might want to consider creating a permanent sequence table instead of building it on the fly like I am doing in that code above. If do want to build it on the fly though, you may have to add hundreds, thousands etc to it (depending on how high your gnumber gets).

Edited by - izaltsman on 01/15/2002 17:02:40

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-15 : 17:16:57

skillile,

Here is another way..


insert into #regulartimes
Select gtime from #default where gnumber >= 1
union all
Select gtime from #default where gnumber >= 2
union all
Select gtime from #default where gnumber >= 3

DavidM

Tomorrow is the same day as Today was the day before.

Edited by - byrmol on 01/15/2002 17:17:24

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-01-15 : 18:18:51

Performance Tip
If you are using SQL 2000, use Table Variables instead of #temp tables. Temp tables are written to temp DB, so there is IO that must occur. Table variables are basically in-memory temp tables. The table never hits the disk, so they are a bit faster than temp tables.

Be sure to do a search in the BOL and on sqlteam.com for more info/examples.

DECLARE @mytable table (field1 varchar(50), field2(datetime))

INSERT INTO @mytable(field1, field2) VALUES('test', '1/15/2002')
INSERT INTO @mytable(field1, field2) VALUES('some more text', '1/15/2002')
INSERT INTO @mytable(field1, field2) VALUES('other text', '1/15/2002')

SELECT * from @MyTable
Michael

skillile
Posting Yak Master

208 Posts

Posted - 2002-01-16 : 08:07:38

OK, The solutions work and the more I look at it, I need to retool my solution.

Here is my objective:

I have an office that has default times they make available to the public. These times are day specific not date specific. My input screen will look like this.

8:00 pick how many times to make avail 2
9:00 pick how many times to make avail 1
10:00 pick how many times to make avail 0

(so 3 records total should go into a availschedule table)

now as I pass these into SQL I want to split them into individual records. Do I need to make variables such as this:

(
@t800 vc,
@t800val int,
@t900 vc,
@t900val int,
etc.

)

and then do a while statement. Is there a more efficient strategy to achieve this goal?

Thanks

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources