Insert Into Select Get ID From another Table

Question

I have a DB that we built a few years ago that we're placing into an RDB. I want the following:

PlantName  PlantNumber  Customer  ProductNumber  Product
________________________________________________________
________________________________________________________
Loc A      1000         Joe's     123456         Pizza 1
________________________________________________________
Loc B      2000         Jerry's   654321         Pizza 2

Which is one table, to go into a new database with Plants, Customers and Products Tables. I have the Customers table already worked out:

CustomerID    CustomerName
____________________________
____________________________
987           Joe's
____________________________
789           Jerry's

And I want the Products Table to look as such

ProductID    CustomerID    ProductName    
_______________________________________
_______________________________________
123456       987           Pizza 1
_______________________________________
654321       789           Pizza 2

So my question is, how can I create an INSERT Statement to get the CustomerID from the Customers Table? I would need to select both from the old Products Table and the new Customers Table to ge the ProductID and the CustomerID values.

Uh, no. You want a `products` table and a `customerProducts` table -- two reference tables and a table that correlates them. — Gordon Linoff, Jan 23 '18 at 19:26
You are right. I will want it set up like that. I'm just asking more as a technical SQL question. Because I will need to get two ID's from two different tables. Thanks for the tip, though — jDave1984, Jan 23 '18 at 19:37

score 0 · Answer 1 · answered Jan 23 '18 at 19:27

0

You can use regular INSERT syntax using a nested SELECT statement. See this link for example: Insert into ... values ( SELECT ... FROM ... )

INSERT INTO Products ( ProductID, CustomerID, ProductName )
SELECT  12345, CustomerID, 'Product name'
FROM    Customers
WHERE   CustomerName = 'name'

But as the comment suggests, you'd probably want to create a third mapping table that handles this data for you. One table strictly for customers, one for products, and a third one for purchases/customer product mapping.

You might also be interested in normal forms for databases: https://en.wikipedia.org/wiki/First_normal_form which can help you design your tables in a cleaner, easier to maintain fashion.

answered Jan 23 '18 at 19:27

aphrid

589
8
25

So if I need to get the `ProductID` from the old table, do I just plop a `SELECT` statement or do I need to Join two tables? – jDave1984 Jan 23 '18 at 19:35
1

If you need the `ProductID`, then you'd have to join the two tables. – aphrid Jan 23 '18 at 19:48
Okay that's the part I'm iffy about – jDave1984 Jan 23 '18 at 19:51

score 0 · Answer 2 · answered Jan 23 '18 at 19:40

You can use SELECT INTO statement.

SELECT A.ProductNumber as ProductID, B.CustomerID, A.Product as ProductName 
INTO Products
FROM Plants as A
INNER JOIN Customers as B
ON B.CustomerName = A.Customer

The drawback is if CustomerName is not unique and refers to a different person. So the good design is your original table has CustomerId instead CustomerName.

Insert Into Select Get ID From another Table

2 Answers2