Split Row and Paste to Different Tables Based on Column

I have a table like this. Table is populated each time an order is complete. One order can have one or many compartments.

+---------+-------+-------------+------+
| OrderID | Plant | Compartment | Qty  |
+---------+-------+-------------+------+
|      91 |    12 |           1 | 2000 |
|      91 |    12 |           2 | 2000 |
|      91 |    12 |           3 | 2000 |
|      90 |    12 |           1 | 3000 |
|      89 |    12 |           1 | 5000 |
+---------+-------+-------------+------+

Please help write an SQL script that takes the above and splits it into two new tables like so:

Table 1

+---------+-------+
| OrderID | Plant |
+---------+-------+
|      91 |    12 |
|      90 |    12 |
|      89 |    12 |
+---------+-------+

Table 2

+---------+-------------+------+
| OrderID | Compartment | Qty  |
+---------+-------------+------+
|      91 |           1 | 2000 |
|      91 |           2 | 2000 |
|      91 |           3 | 2000 |
|      90 |           1 | 3000 |
|      89 |           1 | 5000 |
+---------+-------------+------+

I’ve tried using the DISTINCT command as suggested;

SELECT * FROM table 
WHERE [OrderID] = (SELECT DISTINCT OrderID from table where (COMPARTMENT = '1'))

Which returns the error;

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If the script can keep track of already processed rows so as to avoid duplication each time it runs, that would be the icing on the cake.

Answer

This is how I’d do:

-- just for debugging, your table is my @table variable table
set nocount on
declare @table table (OrderId int, Plant int, Compartment int, Qty int)

insert into @table values (89, 12, 1, 5000)
insert into @table values (90, 12, 1, 3000)
insert into @table values (91, 12, 3, 2000)
insert into @table values (91, 12, 2, 2000)
insert into @table values (91, 12, 1, 2000)
insert into @table values (91, 12, 2, 3000)

set nocount off 
--select * from @table

-- now here it comes selections:
if (exists (select * 
                 from INFORMATION_SCHEMA.TABLES 
                 where TABLE_SCHEMA = 'dbo' -- or your schema 
                 and TABLE_NAME = 'THeader' -- or your header's table))
    insert into THeader
    select distinct t1.OrderId, t1.Plant 
    from @table t1
    left join THeader t2 on t1.OrderId = t2.OrderId and t1.Plant = t2.Plant
    where t2.OrderId is null
else
    select distinct t1.OrderId, t1.Plant 
    into THeader
    from @table t1
    left join THeader t2 on t1.OrderId = t2.OrderId and t1.Plant = t2.Plant
    where t2.OrderId is null

if (exists (select * 
                 from INFORMATION_SCHEMA.TABLES 
                 where TABLE_SCHEMA = 'dbo' -- or your schema 
                 and TABLE_NAME = 'TChilds' -- or your childs's table))
    insert into TChilds
    select distinct t1.OrderId, t1.Compartment, t1.Qty 
    from @table t1
    left join TChilds t2 on t1.OrderId = t2.OrderId and t1.Compartment = t2.Compartment and t1.Qty = t2.Qty
    where t2.OrderId is null
else
    select distinct t1.OrderId, t1.Compartment, t1.Qty 
    into TChilds
    from @table t1
    left join TChilds t2 on t1.OrderId = t2.OrderId and t1.Compartment = t2.Compartment and t1.Qty = t2.Qty
    where t2.OrderId is null

-- just for debugging
select * from THeader
select * from TChilds

Edit: Even so, I see your master table as a child table, from where you must create Header’s table. It’s enough. I mean your Table can be my TChilds table, and key can be OrderId + Plant. (I don’t know what means plant in this table)