


IF NOT EXISTS
(
  SELECT * FROM sys.tables Where Name = 'AllotmentHotelXTAB'
)
BEGIN
	DECLARE @SQL VARCHAR(MAX)
	CREATE TABLE [dbo].[AllotmentHotelXTAB](
		[AllotmentNumber] [int] NOT NULL,
		[HotelCustomerNumber] [int] NOT NULL,
	 CONSTRAINT [PK_AllotmentHotelXTAB] PRIMARY KEY CLUSTERED 
	(
		[AllotmentNumber] ASC,
		[HotelCustomerNumber] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[AllotmentHotelXTAB]  WITH CHECK ADD  CONSTRAINT [FK_AllotmentHotelXTAB_Allotments] FOREIGN KEY([AllotmentNumber])
	REFERENCES [dbo].[Allotments] ([AllotmentNumber])

	ALTER TABLE [dbo].[AllotmentHotelXTAB] CHECK CONSTRAINT [FK_AllotmentHotelXTAB_Allotments]

	ALTER TABLE [dbo].[AllotmentHotelXTAB]  WITH CHECK ADD  CONSTRAINT [FK_AllotmentHotelXTAB_Customer] FOREIGN KEY([HotelCustomerNumber])
	REFERENCES [dbo].[Customer] ([CustomerNumber])

	ALTER TABLE [dbo].[AllotmentHotelXTAB] CHECK CONSTRAINT [FK_AllotmentHotelXTAB_Customer]

	SET @SQL = 'INSERT INTO AllotmentHotelXTAB (AllotmentNumber, HotelCustomerNumber)
	SELECT AllotmentNumber, (SELECT CustomerNumber FROM Customer C (NOLOCK) Where ISNULL(A.HotelNumber, 0) = C.HotelNumber AND MARKASDELETED = 0)
	FROM ALLOTMENTS A (NOLOCk)'

	EXEC(@SQL)

	EXEC sp_RENAME 'Allotments.HotelNumber' , 'zzzHotelNumber', 'COLUMN' 



END

if not Exists(SELECT * FROM sys.tables Where Name = 'AllotmentHotelNote')
BEGIN
	CREATE TABLE [dbo].[AllotmentHotelNote](
		[FK_AllotmentNumber] [int] NOT NULL,
		[FK_HotelCustomerNumber] [int] NOT NULL,
		[DateStamp] DATETIME NOT NULL DEFAULT 0,
		[Note] nvarchar(max) NULL,
	 CONSTRAINT [PK_AllotmentHotelNoteXTAB] PRIMARY KEY CLUSTERED 
	(
		[FK_AllotmentNumber] ASC,
		[FK_HotelCustomerNumber] ASC,
		[DateStamp] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END


