How to INSERT multiple rows as specified in a tbl

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)

How to INSERT multiple rows as specified in a tbl

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-04 : 05:26:27

Here's my scenario in plain English, leaving out the unnecessary code. I'm designing a SP which compares one table with another and inserts rows based on the resulting query.

The 'Minimums' table holds details of how many staff need to work on each day. i.e.
4-Apr-2006, 8,Cashiers
4-Apr-2006,10,Assistants
4-Apr-2006, 5,Supervisor

The 'Days' table needs to have a row for each worker/slot, so in the example above at least 23 rows should exist.

I've made a select query which compares Minimums and Days tables to produce a 'needed' quantity value if their aren't enough matching rows in Days. So my query looks like this (pseudo-code)
2, Cashiers
3, Assistants
1, Supervisors

Everything is working fine to this point, but now I need a technique for creating these 6 rows. Hoping to do without loops if I can avoid it.

Any ideas?

Thanks
Mark

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-04 : 05:31:18

Somthing like this..

Insert Into TblName(Column1,Column2...)
Select Column1,Column2... From TblName Where..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-04 : 05:45:41

Thanks, but the problem is that the derived table contains a value representing the number of times a line needs to be inserted.

Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 05:47:13

Can you post your table structure, some sample data and the desire result ?

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-04 : 07:40:44

Ok, I'll post a simple example, rather than posting the actual code. If I can find a solution to this I can probably apply it to the real thing.

CREATE TABLE Minimums
(
Qty integer,
Job integer
)
GO
INSERT INTO Minumums (Qty,Job) VALUES (2,1001)
INSERT INTO Minumums (Qty,Job) VALUES (3,1002)
INSERT INTO Minumums (Qty,Job) VALUES (1,1003)

CREATE TABLE Days
(
theday datetime,
UserID integer,
JobID integer
)
GO

The SP needs to create 6 rows in the Days table, based on the Qty data in the Minimums table.

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 08:13:09

How do you get the UserID from Days table ?

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-04 : 10:12:42

The userID will just be 0 for this insert. It's really not important here.

Thanks
Mark

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 10:49:11

And What would be "theday" data, when inserting to "Days" ?
Please post data u expect to be inserted to Days, based on the Data u provided
[Also mention about "Any data" where ever OK to insert. eg. UserID = 0]

Srinika

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-04 : 11:14:47

I'm trying to get (in Days) the following, based on the example above.

04-Apr-2006,0,1001
04-Apr-2006,0,1001
04-Apr-2006,0,1002
04-Apr-2006,0,1002
04-Apr-2006,0,1002
04-Apr-2006,0,1003
04-Apr-2006,0,1004

The date is any supplied date variable.
Thanks
Mark

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 11:25:17

Only way I can think of is using a Cursor.
If any other do not suggest another way and if u r ok with a cursor solution let me know

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 12:03:14

You can get the F_TABLE_NUMBER_RANGE from the script library http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

insert into Days
select	'2006-04-04', 0, Job
from	dbo.F_TABLE_NUMBER_RANGE(1,100) n
inner join Minimums m
on	n.NUMBER <= m.Qty
order by Job

mark1504
Posting Yak Master

103 Posts

Posted - 2006-04-05 : 06:34:24

Thank you very much, KH. The example works as required.

Actually I'd dabbled with a numbers table recently, but still lacked the knowledge to use it in the context.

Regards
Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources