PRINT '--'
PRINT '-- Create a new [STAGE].[MAKES] table.'
PRINT '--'
PRINT ' '

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STAGE].[MAKES]') AND type in (N'U'))
DROP TABLE [STAGE].[MAKES]
GO

-- Create makes table, add constraints with alter
CREATE TABLE [STAGE].[MAKES]
(
    MAKER_ID INT IDENTITY(1,1),
    MAKER_NM VARCHAR(25),
    START_YR SMALLINT,
    END_YR SMALLINT 
) 
GO



PRINT '--'
PRINT '-- Alter [STAGE].[MAKES] table to add constraints'
PRINT '--'
PRINT ' '


-- User defined constraint
ALTER TABLE [STAGE].[MAKES]
ADD CONSTRAINT STG_CHK_START_YR CHECK (ISNULL(START_YR, 0) >= 1903 and ISNULL(START_YR, 0) <= YEAR(GETDATE()) + 1);
GO

-- User defined constraint
ALTER TABLE [STAGE].[MAKES]
ADD CONSTRAINT STG_CHK_END_YR CHECK ( (END_YR IS NULL) OR (END_YR >= 1903 AND END_YR <= YEAR(GETDATE()) + 1) );
GO

-- Entity constraint - natural key
ALTER TABLE [STAGE].[MAKES]
ADD CONSTRAINT STG_UNQ_MAKER_NM UNIQUE (MAKER_NM); 
GO

-- Entity constraint - surrogate key
ALTER TABLE [STAGE].[MAKES]
ADD CONSTRAINT STG_PK_MAKER_ID PRIMARY KEY CLUSTERED (MAKER_ID);
GO


PRINT '--'
PRINT '-- Insert data from [ACTIVE] to [STAGE]'
PRINT '--'
PRINT ' '

INSERT INTO [STAGE].[MAKES] (MAKER_NM, START_YR, END_YR)
SELECT MAKER_NM, START_YR, END_YR 
FROM [ACTIVE].[MAKES]
GO



PRINT '--'
PRINT '-- Show data in [STAGE].[MAKES] table'
PRINT '--'
PRINT ' '

SELECT * FROM [STAGE].[MAKES]
go



PRINT '--'
PRINT '-- Remove [STAGE].[MAKES] table'
PRINT '--'
PRINT ' '

DROP TABLE [STAGE].[MAKES]
go