Insert using Insert Into and Inner Join

Question

I'm inserting rows of data from one table's column to another table's column. This is my work done:

Insert into [Inventory](Cost)
Select cast(a.[CCost] as numeric(18,6)) from [InventoryTemp] as a 
Inner join [Inventory] as b on a.[ID] = b.[ID]

I have 10000 rows of data in my [Inventory] table (ID column is filled up) but when the above query was executed, the Cost data started from 10001 until 20000.

Inventory                      InventoryTemp
ID        Cost                 ID         Cost
1                              1          3.12
3                              3          9.90
18                             18         8.80

The result I want

Inventory
ID         Cost
1          3.12
3          9.90
18         8.80

Lot of possible duplicates http://stackoverflow.com/a/2334741/7012137 — Pream, Mar 09 '17 at 10:49
according to Updated info ... use `Update` Instead of `Insert` as next `UPDATE A SET A.Cost = B.Cost FROM Inventory A, InventoryTemp B WHERE A.ID = B.ID` — ahmed abdelqader, Mar 09 '17 at 10:50

score 1 · Answer 1 · edited May 23 '17 at 12:09

If I have read your question correctly, I think you are trying to update the values of the cost column in your Inventory table, based on the values in the InventoryTemp table.

Therefore you want to perform an UPDATE command rather than an INSERT.

An example of this would be:

UPDATE
    Inventory
SET
    Inventory.Cost = InventoryTemp.Cost
FROM
    Inventory
INNER JOIN 
    InventoryTemp 
ON 
    Inventory.ID = InventoryTemp.ID

For more info please see this question: How do I UPDATE from a SELECT in SQL Server?

score 0 · Answer 2 · answered Mar 09 '17 at 10:51

0

You need to use UPDATE instead of `INSERT'

UPDATE i
SET [Cost] = it.[Cost]
FROM [Inventory] i
INNER JOIN [InventoryTemp] it
ON i.ID = it.ID

answered Mar 09 '17 at 10:51

Venu

455
2
7

score 0 · Answer 3 · answered Mar 09 '17 at 13:29

0

Try use update.

UPDATE b
SET b.Cost = a.Cost
FROM
[InventoryTemp] as a 
Inner join [Inventory] as b on a.[ID] = b.[ID]

answered Mar 09 '17 at 13:29

Sergio Camillo Jr

1

Insert using Insert Into and Inner Join

3 Answers3