--LHA 11. maj 2011

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FKProjectNumberPK'
    AND SysObjects.Name = 'FOLIOS'
)

ALTER TABLE dbo.FOLIOS ADD	FKProjectNumberPK smallint NULL
GO
 
-- ALTER TABLE dbo.FOLIOS ADD CONSTRAINT	FK_FOLIOS_ProjectCodes FOREIGN KEY (FKProjectNumberPK) REFERENCES dbo.ProjectCodes
--	(PK_ProjectCode) 
--	ON UPDATE  NO ACTION 
--	ON DELETE  NO ACTION 
-- GO


IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysObjects.Name = 'FolioProjectXtab'
)
begin
    --------------------------------------------------------------------------------------------------------------
    --																											--
    --  Copying data from FolioProjectXtab to Folios. Hence FolioProjectXtab should nok exist in version 7.x    --
    --                                                                                                          --
    --------------------------------------------------------------------------------------------------------------

Declare @afolionumber int
Declare @aProjectNumber int

DECLARE CopyCur CURSOR FORWARD_ONLY FOR
Select ProjectNumber, folionumber from FolioProjectXtab with (nolock)

Open CopyCur
Fetch Next From CopyCur
Into @aProjectNumber, @afolionumber

While (@@Fetch_status=0)
Begin
  Update folios
  set FKProjectNumberPK = @aProjectNumber
  where FolioNumber= @afolionumber
  
  Fetch Next From CopyCur
  Into @aProjectNumber, @afolionumber
end

Close CopyCur
DeAllocate CopyCur
end
