SQL , INSERT with SELECT

Question

I'm trying to INSERT new row with this values (hotelNo,guestNo,dataform,dataTo,roomNo) I know the hotel name , so I have to SELECT the hotelNo from another table , it didn't work with me , is there something wrong?

INSERT INTO Booking
VALUES (hotelNo,123,'3-sept-1014','3-sept-1014',121)
(SELECT hotelNo
 FROM Hotel
 WHERE hotelName='Ritz Carlton' AND city='Dubai');

possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — Unix von Bash, Apr 03 '14 at 17:57

Raging Bull · Accepted Answer · 2015-06-24T05:27:15.313

5

Remove VALUES (hotelNo,... from your query and you are good to go.

INSERT INTO Booking
(SELECT hotelNo,123,'3-sept-1014','3-sept-1014',121
 FROM Hotel
 WHERE hotelName='Ritz Carlton' AND city='Dubai')

edited Jun 24 '15 at 05:27

answered Apr 03 '14 at 17:55

Raging Bull

18,593
13
50
55

this wont work, you should remove `VALUES` from query – Jerko W. Tisler Apr 03 '14 at 18:03
@BSMN: Are you sure you have 5 fields in the table? – Raging Bull Apr 03 '14 at 18:16
@RagingBull yes I'm sure :( – BSMN Apr 03 '14 at 19:16

score 3 · Answer 2 · answered Apr 03 '14 at 18:01

3

You should do it without VALUES

INSERT INTO Booking 
(SELECT hotelNo, 123, '3-sept-1014','3-sept-1014',121
 FROM Hotel
 WHERE hotelName='Ritz Carlton' AND city='Dubai');

answered Apr 03 '14 at 18:01

Jerko W. Tisler

996
9
29

thank you but it's not work , it gives me this error: SQL command not properly ended – BSMN Apr 03 '14 at 18:05
are you using more than one query? if so you should end every with `;` – Jerko W. Tisler Apr 03 '14 at 18:08
check this out http://stackoverflow.com/questions/72151/ora-00933-sql-command-not-properly-ended – Jerko W. Tisler Apr 03 '14 at 18:28
no I copy your statements and I have just one query – BSMN Apr 03 '14 at 19:22

score 1 · Answer 3 · answered Apr 04 '14 at 00:06

1

Try this:

INSERT INTO Booking VALUES (
(SELECT hotelNo
FROM Hotel
WHERE hotelName='Ritz Carlton' AND city='Dubai'),
123,'3-sept-1014','3-sept-1014',121);

answered Apr 04 '14 at 00:06

Andrew

53
6

SQL , INSERT with SELECT

3 Answers3