How can I insert from multiple tables into one?
I have a table for users (username, password, etc.) and another one for products (product name, price, quantity, username , etc.)
I want the username from the first table to be inserted (along with the rest of the product information) into the products table when the user puts it up for sale.
How can i do this?
I am using Visual Web Developer 2008 Express
a source to share
If you have Linq (.NET 3.5 / C # 3.0):
var users = from u in userTable
from p in productTable
where p.username = u.username
select new productDetails()
{
username = u.username,
price = p.price
}
then newtable.insertAllOnSubmit (users);
if in straight sql
insert into newtable
select table1.value1, table2.value1
from table1, table2
where table1.username = table2.username
Edit:
to clarify ... value1 and value2 are columns from the respective cases. You will have one for each parameter you want to insert. And your new table will become table1
a source to share
Your question is meaningless. It looks like you want to insert into one table ( Products
) and one of the fields is also in another table ( User.username
). Where does the rest of the data for Products
? Can't you just paste the username directly?
Insert into products (name, price, username) values (?, ?, ?)
Use whatever mechanism you want to pass in data to this SQL statement.
If, however, you do have a third table, say " Prod_for_sale
", and you want to insert a bunch of data into that table, both from Users
and Products
, you can do something like this (as Rick J suggested, but this example has two tables):
insert into products for sale (username, user_country, product_name, product_price)
select u.username, u.country, p.name, p.price
from products p, users u
where p.id = ?
and u.username = ?
Then you can copy any data from the required tables to the target table.
A word of advice: you should use a user id instead of a username to identify user data in the database. This will allow you to change your username policy later, or allow username updates, etc. As a matter of fact, try to do all of your ID numbers and you will get rid of problems in the future.
a source to share