SQL insert query with text value

Question

Is there a way that i can Insert this to database?

INSERT INTO Bill_Table (Patient_ID, [Med/Room], Bill, [Paid/UnPaid])
VALUES (13, 'Room ID' + (SELECT Room_ID 
                         FROM Room_Master 
                         WHERE [Room_No.] = 2),
        (SELECT Price  
         FROM Room_Type 
         WHERE Room_Type = 'Semi-Private'), 'UnPaid');

after the ' 13 ' on the values there is a string "Room ID" plus the selected "Room ID" but when I execute this only the Room ID inserted on the table is there a way I can put string text plus the select query

The result here

I removed the mysql tag, because MySQL does not support square brackets. — Gordon Linoff, Mar 02 '19 at 14:45

score 0 · Answer 1 · answered Mar 02 '19 at 14:42

0

Use insert . . . select:

insert Into Bill_Table (Patient_ID, [Med/Room], Bill,[Paid/UnPaid])
    Select 13, concat('Room ID', rm.Room_ID), rt.price, 'UnPaid'
    from Room_Master rm left join
         Room_Type rt
         on rt.Room_Type = 'Semi-Private'
    where rm.[Room_No.] = 2;

In SQLite, you would use the concat operator:

Insert Into Bill_Table (Patient_ID, [Med/Room], Bill,[Paid/UnPaid])
    Select 13, 'Room ID' || rm.Room_ID, rt.price, 'UnPaid'
    from Room_Master rm left join
         Room_Type rt
         on rt.Room_Type = 'Semi-Private'
    where rm.[Room_No.] = 2;

answered Mar 02 '19 at 14:42

Gordon Linoff

1,242,037
58
646
786

Sorry sir but there is no concat function on sqlite but Thank you sir btw – Dummy Dummy Mar 02 '19 at 14:52
@DummyDummy . . . That is why there is a second answer for SQLite. Your original question was not clear on the database you are using -- in fact, it was I who removed the MySQL tag. – Gordon Linoff Mar 02 '19 at 16:27

score 0 · Accepted Answer · answered Mar 02 '19 at 14:48

0

The only problem with your code is the use of + for cocatenation, which does not work in SQLite.
Instead use the || operator:

Insert Into Bill_Table (Patient_ID,[Med/Room],Bill,[Paid/UnPaid])
  values (
    13,
    'Room ID' || ' ' || (Select Room_ID from Room_Master where [Room_No.] = 2),
    (Select Price from Room_Type where Room_Type = 'Semi-Private'),
    'UnPaid'
  );

answered Mar 02 '19 at 14:48

forpas

160,666
10
38
76

this is what I mean haha thank youuuu! sir this really help – Dummy Dummy Mar 02 '19 at 14:53

SQL insert query with text value

2 Answers2