insert into one table from another table using join update or insert SQL server

Question

I currently have two tables as seen in the pictures below.

The first table is called bottle:

The second table is called cases:

I am trying to insert into the bottles table where case_id = 0 the values seen in the cases table (ie case_id=100, 100, 103, 103 etc..). They are two separate tables and I am trying to join them without creating NULL values.

Please let me know if I need to use insert, join, or update. I would manually do it however these data points are coming live from a PLC and there will be many more than just all the data points seen below.

Answer 1

There is no explicit join column with which to perform the UPDATE you want. But we can create one by joining on the row number value from each table, using a certain order. In the query below, I order the row number in the bottle table using the bottle_id, and the row number in the cases table using the case_id.

UPDATE t1
SET t1.case_id = t2.case_id
FROM
(
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY bottle_id) rowId
    FROM bottle t
) t1
INNER JOIN
(
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY case_id) rowId
    cases t
) t2
    ON t1.rowId = t2.rowId

This should give output in bottle looking like this (some columns omitted):

bottle_id      case_id
1              100
2              100
3              103
4              103
5              104
6              104
7              105
8              105