Question Inserting fetched and created data

[mauni]

Hi

I have table USERS

USERS
userid
username

I want to fetch information from this table, and insert it into another table called BILLING

BILLING
billid (auto-increment)
billnote
userid
username


I can insert the user information with this SQL:

insert into billing (userid, username)
select (userid, username FROM USERS);


But how can i add information for the additional field "billnote"?

I want to add note "This bill needs to invoice next month" into "billnote" field. What is the SQL syntax?

 

[HiTechCoach]

Hi

I have table USERS

USERS
userid
username

I want to fetch information from this table, and insert it into another table called BILLING

BILLING
billid (auto-increment)
billnote
userid
username


I can insert the user information with this SQL:

insert into billing (userid, username)
select (userid, username FROM USERS);


But how can i add information for the additional field "billnote"?

I want to add note "This bill needs to invoice next month" into "billnote" field. What is the SQL syntax?

Normally you should not be storing th user name in multiple places, only the user id. When you need the user name, you will just lookup the name form the table users.

You current SQL will insert a record for every user in the table USERS. Is this what you want?

Also, where is the Billing table's foreign key so that it is related to something.

Using your SQL without the duplicating of the name and adding the note, but still for all the users:

insert into billing (userid, BillNote)
select (userid, "This bill needs to invoice next month"  as BillNote FROM USERS);

If there are three users, then it wouldl insert this:

1 "This bill needs to invoice next month"
2 "This bill needs to invoice next month"
3 "This bill needs to invoice next month"

There is nothing that identifies which bill? Is there only one bill?

 

[mauni]

HiThe tables are more complex in real life, than in my example. I just needed to get the SQL syntax, which you have now provided. Thanks.

 

[HiTechCoach]

You're welcome!

Glad you got it figured out!