CAMS - Import Fund Appeal Codes

USE [CAMS_Enterprise]
GO
/****** Object:  StoredProcedure [dbo].[PHC_spImportFundAppealCodes]    Script Date: 04/30/2012 13:58:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PHC_spImportFundAppealCodes]
    @File varchar(100),
    @TableName varchar(50)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(8000)
DECLARE @RowMax int
Declare @elemnum as int
DECLARE @Code as varchar(50)
DECLARE @Segment as varchar(50)

SET @SQL = 'Insert into ' + @TableName + ' Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,''Excel 8.0;Database='+ @File + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
EXEC (@SQL)

DECLARE MyCursor cursor fast_forward for

SELECT Code,Segment FROM PHC_tmpImportFundAppealCodes
open MyCursor
fetch next from MyCursor
into @Code,@Segment

Set @RowMax = (SELECT COUNT(*) FROM PHC_tmpImportFundAppealCodes)
while @@fetch_status = 0
BEGIN
    SET @elemnum = (SELECT MAX(ElementNo) From Glossary Where Category = 52)+ 1
    INSERT INTO Glossary (ElementNo, Category, DisplayText, [Description])
    VALUES (@elemnum, 52, CAST(@Code AS nvarchar(50)), CAST(@Segment AS nvarchar(50)))
    fetch next from MyCursor
    into @Code,@Segment
END
close MyCursor
deallocate MyCursor
TRUNCATE TABLE PHC_tmpImportFundAppealCodes
END