CODEWITHSUNDEEP
sqlsql-server
garlic
garlic

Reputation: 197

Insert one table from one table to another table

I'm using MS SQL Server Express 2017.

I have an OrderDetails table.

OrderDetailID   OrderID ProductID   Quantity    Category
1               1       1           5           Local
2               1       3           2           Remote
3               1       2           5           Store
4               2       1           3           Local
5               2       2           5           Remote
6               2       3           2           Remote

I want to insert a new order detail with a category of 'Unavailable', by using an existing list of OrderID's. The list of OrderID's that I have are in a temp table with Int values for OrderID's that are already in the OrderDetails table.

For example: If in my temp table of order ID's I have 1 and 2 (orderID 1 and 2), I'd like to insert another OrderID 1 and OrderID 2 to the OrderDetails Table with the values I want, such as a category of 'Unavailable' (Category has a column data type of varchar(50), but I'd like for the Insert statement to use the same OrderID value from my temp table with existing OrderID's that I want to add.

My temp table (_tmpOrderIDs):

OrderID
1
2

So far I have this statement:

INSERT INTO OrderDetails
   (OrderID, ProductID, Quantity, Category) 
       VALUES ((SELECT OrderID FROM _tmpOrderIDs), 4, 1, 'Unavailable')

The error I get is 'Subquery returned more than 1 value.', which is right, because _tmpOrderIDs has more than one OrderID. But I want all those orderID's in _tmpOrderIDs to be inserted to the OrderDetails table using the custom values I give it, while keeping their own individual OrderID's the same, as they are - in the temp table, _tmpOrderIDs.

Any ideas?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Just use insert . . . select:

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Category) 
    SELECT OrderID,  4, 1, 'Unavailable'
    FROM _tmpOrderIDs;

The VALUES keyword is not needed.

Upvotes: 3

Related Questions