SQL query to INSERT a set of values, multiple times WHERE the number of times and one of the values is determined by the result of the WHERE cause

user_19555952 0
Viewed 49

Trying to make a query to insert same set of values into a table, multiple times, with on of the values being an ID number from another table, and the results of another where clause determining the list of ID's

I was using something similar to the query below to pull up a list of company names from the database that have a particular type of product type installed (custom pricing etc)
Currently the list would be used to manually add a new product to the database for each of the companies in that list (front end only has interface to add from the company record)

I want to add identical products to all of that list of companies, so my plan was to adjust the query below to list the CompanyID numbers and use that list:

Use DBNAME
SELECT DISTINCT CompanyName
FROM CompanyList JOIN ProductList
ON CompanyList.CompanyID = ProductList.CompanyID
WHERE ProductList.ProductTypeID IN (71,72,73)
ORDER BY CompanyName

SET IDENTITY_INSERT [ProductList] ON
    
INSERT [dbo].[ProductList]
([ProductID][Name],[ProductTypeID],[Cost],[WholesaleRate],[UndeID],[Limit],[Days],[SortOrder],[Expired],[CompanyID])
VALUES
(-1,'ProductF',77,3.9,5.75,7,0,0,1,No,???)
    
SET  IDENTITY_INSERT [ProductList] OFF

I expect I need to replace CompanyName with CompanyID in the where, and combine the two in a way that wraps them in a transaction and allows the insert to step through the results of the where, maybe passing each result to the insert as a variable?

But I am not sure where to begin.

Thank you for your help, if anyone else has this issue please see below the final working result derived from the Marked best answer:

Use CVR_Sandbox
begin transaction
   
INSERT INTO [dbo].[ProductList] ([Name], [ProductTypeID], [Cost], [WholesaleRate], [UndeID], [Limit], [Days], [SortOrder], [Expired], [CompanyID])
    SELECT DISTINCT         'TESTPRODUCTname', 79, 3.9, 5.75, 7, 0, 0, 1, 0, cl.CompanyID
    FROM         CompanyList cl
    JOIN         ProductList pl ON cl.CompanyID = pl.CompanyID
    WHERE         pl.ProductTypeID = 71
    ORDER BY         cl.CompanyID

rollback transaction
--commit transaction
sql-server sql
1 Answers

You need to write something like this - an INSERT INTO based on a SELECT, where most of the values are constants (as defined in your second query in your question).

SET IDENTITY_INSERT [ProductList] ON
    
INSERT INTO [dbo].[ProductList] ([ProductID], [Name], [ProductTypeID], [Cost], [WholesaleRate], [UndeID], [Limit], [Days], [SortOrder], [Expired], [CompanyID])
    SELECT DISTINCT 
        -1, 'ProductF', 77, 3.9, 5.75, 7, 0, 0, 1, No, pl.CompanyID
    FROM 
        CompanyList cl
    JOIN 
        ProductList pl ON cl.CompanyID = pl.CompanyID
    WHERE 
        pl.ProductTypeID IN (71, 72, 73)
    ORDER BY 
        cl.CompanyName
    
SET  IDENTITY_INSERT [ProductList] OFF

Not sure why you're doing an IDENTITY_INSERT ON and then inserting the same value -1 for each row...... since identity columns typically are primary keys - this might be a recipe for disaster (and errors) ...... do you REALLY need this?? Why not just let the database hand out the identity values?? That's what you've defined them for, after all .....

user_133020
Related