SET NOCOUNT ON
IF EXISTS (SELECT * FROM Reservations WITH (NOLOCK) WHERE ReservationCreateDate IS NULL)
BEGIN
  UPDATE Reservations
  SET ReservationCreateDate = (
    SELECT TOP 1 CreatedDate 
    FROM ReservationFollowUpXTab WITH (NOLOCK) 
    INNER JOIN FollowUp WITH (NOLOCK) ON ReservationFollowUpXTab.FollowUpNumber = FollowUp.FollowUpNumber
      AND ReferenceNumber = Reservations.ReferenceNumber 
      AND GuestNumber = Reservations.GuestNumber 
      AND Type = 5 
      AND SubType = 1
  )
  WHERE ReservationCreateDate IS NULL

  DECLARE @SystemdateStr CHAR(8)
  DECLARE @SystemDateTime DATETIME

  SELECT TOP 1 @SystemdateStr = SystemDate FROM System WITH (NOLOCK)
  SET @SystemDateTime = CONVERT(DATETIME, @SystemDateStr + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

  UPDATE Reservations
  SET ReservationCreateDate = @SystemDateTime
  WHERE ReservationCreateDate IS NULL

  ALTER TABLE Reservations ALTER COLUMN ReservationCreateDate DATETIME NOT NULL
END

DECLARE @default_name VARCHAR(256)
SELECT @default_name = [name] 
FROM sys.default_constraints
WHERE parent_object_id = object_id('Reservations')
AND col_name(parent_object_id, parent_column_id) = 'ReservationCreateDate'
IF ISNULL(@default_name, '') = ''
  ALTER TABLE Reservations ADD CONSTRAINT DF_Reservations_ReservationCreateDate DEFAULT GETDATE() FOR ReservationCreateDate;
GO