How can I insert from multiple tables into one?

Question

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 (with the rest of the product info) into the product table when the user put it for sale.

How can I do this?

I'm using visual web developer 2008 express

Answer 1

INSERT INTO product_information (username,product_name, ...., Date)
SELECT username, 'Book',....., Date
FROM users;

something along that line

Answer 2

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 corresponding cases. You'll have one for each parameter you want to insert. And your newtable becomes table1

Answer 3

Your question doesn't exactly make sense. It sounds 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 come from? Can't you just insert the username directly?

Insert into products (name, price, username) values (?, ?, ?)

Use whatever mechanism you want to pass in the data to that SQL statement.

If, however, you actually have a third table, say, 'Prod_for_sale' and you want to insert a bunch of data into that table, from both Users and Products, you can do something like this (as suggested by Rick J, 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 whatever data you want from the tables in question into the destination table.

A word of advice though: you should be using a user ID instead of a username to identify the user's data in the database. This will allow you to change your username policy later or allow for usernames to be updated, etc. Generally try to make all your identifiers numbers and you'll save yourself hassle in the future.