Temp Table Insertion Problem

This is my code through which I insert the data into herm_Gen_Liab_Pre table.set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER proc [dbo].[sample]

--@policyno varchar(9),-- FOR MOLD COVERAGE

--@formno varchar(6) -- FOR MOLD COVERAGE

as

TRUNCATE TABLE herm_Gen_Liab_Pre

SET ANSI_WARNINGS OFF

SET ARITHABORT OFF

DECLARE @genliabpre TABLE

( accmnth varchar(2),

accyear varchar (4),

treffmnth varchar (2),

treffyr varchar (4),

trexmnth varchar (2),

trexyr varchar (4),

statecode varchar (2),

typeofpolicy varchar(2),

exposure varchar(7),

annualstatementlineofbusinesscode varchar(3),

PremiumRecordId varchar (14),

Sublinecode varchar (3),

classcode varchar (5),

incemnth varchar (2),

inceyr varchar (4),

territorycode varchar(3)

--policyno varchar(9),-- for mold coverage

--formno varchar(6), -- for mold coverage

--moldcoverage varchar(1) -- mold coverage value

);

insert into @genliabpre (accmnth,accyear,treffmnth,treffyr,trexmnth,trexyr,

statecode,typeofpolicy,exposure,annualstatementlineofbusinesscode,PremiumRecordId,

Sublinecode,classcode,incemnth,inceyr,territorycode)

select datepart(m,p.EntryDate),

datepart(yy,p.EntryDate),

datepart(m,p.PremiumEffectiveDate),

datepart(yy,p.PremiumEffectiveDate),

datepart(m,p.PolicyExpirationDate),

datepart(yy,p.PolicyExpirationDate),

p.statecode,p.policytypecode,p.ExposureLimitAmt,s.lineofbusinesscode,

s.invoiceno,s.sublinecode,s.ClassCode,

datepart(m,p.PolicyOriginalEffectiveDate),

datepart(yy,p.PolicyOriginalEffectiveDate),s.TerritoryCode

from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s

on p.invoiceno = s.invoiceno

where

s.lineofbusinesscode in ('052','152','170','171','175','180')--,'192','194') -- general liability

AND P.STATECODE IN ('NY','NJ','PA','RI','GA')

and p.entrydate between '01/01/2004' and '12/31/2007'

order by p.entrydate

----------------------------INSERT MOLD COVERAGE--------------------------------

/*

SET @policyno = (select cast(pd.policyno as varchar(9))

from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf

on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from HERMITAGE.DBO.premiumdirect p join

HERMITAGE.DBO.premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

)



SET @FORMNO = (select PF.FORMNO

from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf

on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from HERMITAGE.DBO.premiumdirect p join

HERMITAGE.DBO.premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

)

*/

---------------------------------------------------------------------------------

insert into herm_Gen_Liab_Pre (

AccountingDate,

TransactionEffectiveDate,

TransactionExpirationDate,

StateCode,

typeofpolicy,

exposure,

AnnualStatementLineOfBusinessCode,

PremiumRecordId,

SublineCode,

ClassificationCode,

InceptionDate,

territorycode

)

select accmnth= case accmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end + + SUBSTRING(accyear,4,1),

treffmnth=case treffmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end + + substring(treffyr,3,2),

trexmnth = case trexmnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end+ + substring(trexyr,3,3),statecode,

typeofpolicy,exposure,AnnualStatementLineOfBusinessCode,premiumrecordid,Sublinecode,

classcode,incemnth = case incemnth

when '1' then '1'

when '2' then '2'

when '3' then '3'

when '4' then '4'

when '5' then '5'

when '6' then '6'

when '7' then '7'

when '8' then '8'

when '9' then '9'

when '10' then '0'

when '11' then '-'

when '12' then '&' end+ + substring(inceyr,3,2),TerritoryCode

from @genliabpre

exec genliab_state

update herm_Gen_Liab_Pre

set territorycode = '999' where statecode = '31'



update herm_Gen_Liab_Pre

set TransactionTypecode ='1'

----------------------STATE EXCEPTION INDICATOR CODE-----------------------------

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode = '325' and classificationcode in ('99935','99936') and statecode = '20'

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode = '334'

and classificationcode in ('01412','01418','05113','05114','05118','05123','05124'

,'05125','05213','05223','05224','60010','60011','62003','63010','63011'

,'63012','63013','64500') and statecode = '20'

update herm_Gen_Liab_Pre

set StateExceptionIndicator = '1' where sublinecode in ('345','347') and classificationcode = '80050' and statecode = '20'

select * from herm_Gen_Liab_Pre

The problem is there is a column in this table called riskidCoPaymentPercentageMoldDamageCoverageCode and I have to insert value '1' into that column based on this select query


select cast(pd.policyno as varchar(9)),pf.formno

from premiumdirect pd join policyforms pf on pd.policyno = pf.policyno

where pd.entrydate between '01/01/2004' and '12/31/2007'

and pf.formno in ('CG2167','CG3131')

and exists

( select policyno from premiumdirect p join premiumstatdirect ps

on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)

where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')

and ps.sublinecode in ('334','335','336','337','338','339'))

order by pd.entrydate

How can i insert implement this. I tried temp variable, was successful partially, so need a better approach to it.

Chintan