CustomerSetup

gistfile1.txt

CREATE SCHEMA Customer
    GO
    
    CREATE SCHEMA detail
    go

    CREATE TABLE Customer.Customer
    (
        CustomerId BIGINT IDENTITY(1,1) PRIMARY KEY,
        CurrentCustomerIdentifierId BIGINT NULL       
    )
    go

    CREATE TABLE Customer.CustomerIdentifier
    (
        CustomerIdentifierId BIGINT IDENTITY(1,1) PRIMARY KEY,
        CustomerId BIGINT,

        CONSTRAINT FK_CustomerId FOREIGN KEY (CustomerId) 
            REFERENCES Customer.Customer (CustomerId) 
    )
    go

    ALTER TABLE Customer.Customer ADD CONSTRAINT FK_CurrentCustomerIdentifierId FOREIGN KEY (CurrentCustomerIdentifierId) 
            REFERENCES Customer.CustomerIdentifier (CustomerIdentifierId) 
    GO
    
    CREATE TABLE detail.OrderDetail
    (
        OrderDetailId BIGINT IDENTITY(1,1) PRIMARY KEY,
        CustomerIdentifierId bigint,

        CONSTRAINT FK_CustomerId FOREIGN KEY (CustomerIdentifierId) 
            REFERENCES Customer.CustomerIdentifier (CustomerIdentifierId)
    )
    GO


    INSERT INTO Customer.Customer DEFAULT VALUES
    INSERT INTO Customer.Customer DEFAULT VALUES
    INSERT INTO Customer.Customer DEFAULT VALUES
    INSERT INTO Customer.Customer DEFAULT VALUES
    GO
    
    INSERT INTO Customer.CustomerIdentifier ( CustomerId )
    VALUES  (1),(2),(3),(4),(1),(2),(3),(4),(1),(2),(3),(4)
    GO

    UPDATE Customer.Customer
    -- In real data these would be vary among all the records for the customer
    -- but we can just set them all to 3 except for one (for this example)
    SET CurrentCustomerIdentifierId = 3  
    GO
    

    UPDATE Customer.Customer
    SET CurrentCustomerIdentifierId = 2
    WHERE CustomerId = 4
    GO
    
    
    INSERT INTO detail.OrderDetail( CustomerIdentifierId )
    VALUES  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
            (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
            (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
            (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
            (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)    
    GO