Insert into table using join to resolve a single element

Question

I'm trying to insert into a table while avoiding a secondary query to lookup an id of a product which is contained in another table.

My insert query:

INSERT INTO prod_addfeatures (ProductID, ProdFeature, ProdFeatureTitle) VALUES (?, ?, ?);

ProductID is a Long Integer, the other two are simple strings.

At this point in the program, I only have the string representation of the ProductID (the sku, ex: TB100).

To get the numerical id of the sku, I would do something like:

SELECT products.catalogid FROM products WHERE id = ?;

(id is not a PK, rather catalogid is... this is a vendor table and cannot be changed).

I'm trying to avoid doing the SELECT before running the INSERT in order to avoid the extra network round-trip (remote database), however I can really only seem to dig up examples of selecting into the table, not quite what I'm after.

For clarity:

Two of the Three values being inserted must be supplied at runtime and do not exist in any other table. One of the Three values being inserted must be looked-up at runtime as it resides in another table.

ProductID        <-- Must be selected or joined from the products table
ProdFeature      <-- Supplied at runtime
ProdFeatureTitle <-- Supplied at runtime

So I"m trying to get something like:

INSERT INTO prod_addfeatures (ProductID, ProdFeature, ProdFeatureTitle) 
    VALUES ((SELECT products.catalogid FROM products WHERE products.id = ?), ?, ?);

But I don't believe that is the correct way to go about this.

It's not exactly clear what value you have that can be used to get the ProductID from the product table. — Tab Alleman, Jun 18 '15 at 18:41
@TabAlleman I have `products.id` which is, say, "TB100". The value needed is `products.catalogid` which is, say, "123456". — SnakeDoc, Jun 18 '15 at 18:42

score 2 · Answer 1 · answered Jun 18 '15 at 18:29

2

You can do a sub-select, e.g.,

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
WHERE conditions;

This example from from http://www.techonthenet.com/sql_server/insert.php

answered Jun 18 '15 at 18:29

T.D. Smith

984
2
7
22

I don't know how this would work, since I must supply two of the three values at runtime (they do not exist anywhere), the third value must come from another table from some sort of lookup or join. – SnakeDoc Jun 18 '15 at 18:31
In your example this would look like `INSERT INTO prod_addfeatures (ProductID, ProdFeature, ProdFeatureTitle) VALUES ((SELECT products.catalogid FROM products WHERE id = ?), 'val2', val3')` – T.D. Smith Jun 18 '15 at 18:39
right, that's what I am trying now. It works, but I've always heard doing subqueries are expensive and to join when possible. – SnakeDoc Jun 18 '15 at 18:44
Ah, I misunderstood your question to mean that you were running a second, entirely separate query. [This thread](https://stackoverflow.com/questions/141278/subqueries-vs-joins) implies that the cost of a subquery is greater than a join if you're selecting a lot of rows, but I doubt it would make a big difference in this circumstance. – T.D. Smith Jun 18 '15 at 18:47

score 2 · Accepted Answer · answered Jun 18 '15 at 18:44

According to your comment, this should work:

INSERT INTO prod_addfeatures (ProductID, ProdFeature, ProdFeatureTitle) 
SELECT CatalogId, @ProdFeature, @ProdFeatureTitle
FROM Products
WHERE Id = @ProductId

Assuming that the three parameters are the values you are passing from your app.

There is no need for a join, since there is only one table containing data needed for your insert. The other values come from parameters rather than a table.

Insert into table using join to resolve a single element

2 Answers2