ALTER procedure LoadTablesProc

as
BEGIN
set nocount on

-- question #1
-- ==========

-- room for new custom.s
Declare @CustTable table (
  id int IDENTITY (1,1),
  customerID int,
  customer Varchar(50)
  )

-- new custom.s
insert into @CustTable (Customer)
Select DISTINCT l.Customer 
  from LoadOrder l
    LEFT OUTER JOIN Customer c
      on l.Customer = c.Customer
    Where c.Customer is null

-- new ID for new cust.s
declare @NewCustOffset int
  Select @NewCustOffset = isnull(max(customerid),0) from Customer

-- assign them
update @CustTable
  set CustomerID = id + @NewCustOffset
  where customerID is null

-- if existing
insert into @CustTable 
select distinct c.customerid, c.customer
  from customer c
  inner join loadorder l
  on c.customer = l.customer

-- begin orders

-- room for orders
Declare @order table (
  id int IDENTITY (1,1),
  OrderID int,
  CustomerID int,
  OrderDate datetime
  )

-- insert
insert into @order (CustomerID, OrderDate)

-- new cust
Select c.CustomerID, convert(varchar,l.orderdate,101)
  from @CustTable c
    inner join loadorder l
      on c.customer = l.customer
  group by c.CustomerID, convert(varchar,l.orderdate,101)

-- if existing
Union
  Select c.customerid, convert(varchar,l.orderdate,101)
    from loadorder l
      inner join customer c
        on l.Customer = c.Customer
    group by c.customerid, convert(varchar,l.orderdate,101)

-- orderID
declare @NewOrderOffset int
  Select @NewOrderOffset = isnull(max(orderid),0) from [order]

update @order
  set OrderID = id + @NewOrderOffset

-- begin products

-- room for products
Declare @product table (
  id int IDENTITY (1,1),
  ProductID int,
  Product varchar(50),
  UnitCost money
  )

insert into @product (product,unitcost)
Select distinct l.Product, -1
  from loadorder l
  left outer join product p
    on p.product = l.product
  where p.product is null

-- prod. ID
declare @NewProdOffset int
Select @NewProdOffset = isnull(max(productid),0) from product

update @product 
  set productID = ID + @NewProdOffset

insert into @product (productID, product, unitcost)
Select DISTINCT p.ProductID, p.Product, p.UnitCost
  from Product p
    INNER JOIN loadorder l
      on p.product = l.product


-- get orders

-- room for new orders
declare @orderdetail table(
  id int IDENTITY (1,1),
  OrderDetailID int,
  OrdersID int,
  ProductID int,
  Qty int
  )

insert into @orderDetail (OrdersID, ProductID, Qty)
Select o.OrderID, p.ProductId, lo.Qty
FROM @CustTable c
  inner join loadorder lo
    on c.customer = lo.customer
  inner join @order o
    on o.customerid = C.CUSTOmerID and  
      convert(varchar,lo.orderdate,101)= convert(varchar,o.orderdate,101)
  inner join @product p
    on p.product = lo.product

-- orders' details
declare @NewOrdDetailsOffset int
Select @NewOrdDetailsOffset = isnull(max(orderdetailid),0) from orderdetail

update @orderDetail
  set orderdetailid = ID + @NewOrdDetailsOffset


-- reverte to
insert into Customer
select a.customerid, a.customer
  from @CustTable a
    left outer join customer b
      on a.customerid = b.customerid
  where b.customerid is null

insert into [Order]
select a.orderid, a.customerid, a.orderdate
  from @order a
    left outer join [order] b
      on a.orderid = b.orderid
  where b.orderid is null

insert into product
select a.productid, a.product, a.unitcost
  from @product a
    left outer join product b
      on a.productid = b.productid
  where b.productid is null

insert into OrderDetail
select a.OrderDetailID, a.OrderDetailID, a.ProductID, a.Qty
  from @OrderDetail a
    left outer join OrderDetail b
      on a.OrderDetailID = b.OrderDetailID
  where b.OrderDetailID is null


-- question #2
-- ==========

-- orders
SELECT
  o.OrderID
  FROM dbo.[Order] o
    INNER JOIN dbo.OrderDetail od
      ON o.OrderID = od.OrderID 
    INNER JOIN dbo.Product p
      ON od.ProductID = p.ProductID
  WHERE (p.UnitCost = -1) -- no unit cost


END