Multiple insert - 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

General SQL Server Forums

New to SQL Server Programming

Multiple insert

Author

Topic

swemaz
Starting Member

6 Posts

Posted - 2009-03-09 : 07:04:38

Hi!

I'm trying to create a hotel room reservation schema, but can't figure out how to insert multipel rows. For each room i need to set if the room is booked for the selected period (1 quarter of a year).

This is how it looks in XML

<EventObj Name="Room 1" ID="1">
<ReservedWeeks>
<anyType xsi:type="xsd:int">7</anyType>
<anyType xsi:type="xsd:int">8</anyType>
<anyType xsi:type="xsd:int">9</anyType>
<EventObj Name="Room 2" ID="2">
<ReservedWeeks>
<anyType xsi:type="xsd:int">1</anyType>
<anyType xsi:type="xsd:int">2</anyType>
<anyType xsi:type="xsd:int">3</anyType>

Can someone help me?
//swemaz

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-09 : 07:39:44

Can you clarify - e.g. are you doing an INSERT into one table depending on value in another or are doing an UPDATE of a column depending on the value in other columns. Some more details etc would help.
Generally - to do multiple inserts:
http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

swemaz
Starting Member

6 Posts

Posted - 2009-03-09 : 08:29:16

Ok, I try to clarify.
I have grid showing 19 rooms and 12 weeks where the user can make reservations of a room on selected weeks. When they click save I need to store the data somewhere.

This means that if Room 1 is booked weeks 11, 12, 14, 16 I need to insert it in a table with columns "roomid" and "weeknr"
Then i have to do the same with the next room and so on.

I hope this makes it clearer.
//swemaz

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-09 : 11:48:47

The link showed how to insert multiple rows - here is the relevant section modified for your table:

INSERT INTO RoomBooking (roomid, weeknr)
Select '1', '1'
Union All
Select '2', '5'
Union All
Select '1', '8'
Union All
Select '2', '9'

Note: there is no checking to make sure this room has not been booked for this period - you may want to look at the logic for this especially if application can be used by multiple users.

swemaz
Starting Member

6 Posts

Posted - 2009-03-09 : 13:04:51

Thanks!
I guess I can replace the values with parameters to be recieved from the application, but if I have 19 rooms where each room possibly can have 12 weeks it seems to me that it will be a lot of parameters. Is their a way to solve that? Is it possible to loop trough an array in SQL?

swemaz
Starting Member

6 Posts

Posted - 2009-03-09 : 13:11:41

Here is a test I did that actually worked if I just wanted to insert all the weeks for all rooms
DECLARE @roomid int, @weeknr int, @booked bit
SET @roomid = 0
SET @weeknr = 12
SET @booked = NULL

WHILE @weeknr < 15
BEGIN
SET @weeknr = @weeknr + 1
PRINT 'WeekNr: ' + CAST(@weeknr as varchar(20))
WHILE @roomid < 19
BEGIN
SET @roomid = @roomid + 1
INSERT INTO tblWeeks
(
RoomId,
WeekNr,
Booked
)
VALUES
(
@roomid,
@weeknr,
@booked
)
PRINT ' RoomID: ' + CAST(@roomid as varchar(20))
END
SET @roomid = 0
END

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 06:49:45

Here is an interesting link which has a function which takes a comma seperated list which is manipulated using REPLACE to replace ',' with ' UNION ALL SELECT ' to build the multiple insert statement for a list passed in.

http://www.sommarskog.se/arrays-in-sql-2005.html#listtoselect

swemaz
Starting Member

6 Posts

Posted - 2009-03-11 : 05:44:11

Thanks! That did the trick!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-11 : 07:31:51

welcome

swemaz
Starting Member

6 Posts

Posted - 2009-04-27 : 06:45:07

The link solved my problem! darkdusky you can delete your post

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-27 : 07:59:15

Why would he delete his post?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-27 : 09:13:00

quote:
Originally posted by swemaz

The link solved my problem! darkdusky you can delete your post

So, do you want others not to LEARN?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources