asked on
MS SQL SERVER 2005
I had one table named Policy_Rpt.ILRCL in a database. There is also related procedure to this table named Policy_Rpt.usp_ILRCL.
Generally the table is populated by the procedure.
Now I created One more table named Policy_rpt.Ageny with an extra column called 'Lossdesc' with initial value Null in all the columns..
I populated this table using the following command.
Insert into Policy_rpt.Ageny (columns........) Select * from Policy_Rpt.ILRCL.
Now i need to add the following functionality for the new column 'Loss desc'. How can i do it...
(
LossDesc (ClaimNumber level one record for claim)
From Kaimart_Rpt.ILRCL.ClaimNum
Use column LossDescription in Kaimart.LossDescription
* One value for all rows on a Claim
)
Generally the table is populated by the procedure.
Now I created One more table named Policy_rpt.Ageny with an extra column called 'Lossdesc' with initial value Null in all the columns..
I populated this table using the following command.
Insert into Policy_rpt.Ageny (columns........) Select * from Policy_Rpt.ILRCL.
Now i need to add the following functionality for the new column 'Loss desc'. How can i do it...
(
LossDesc (ClaimNumber level one record for claim)
From Kaimart_Rpt.ILRCL.ClaimNum
Use column LossDescription in Kaimart.LossDescription
* One value for all rows on a Claim
)
Microsoft SQL Server 2005
Last Comment
bsumanrao
Actually the second statement should be as follows -
UPDATE
PA
SET
LossDesc = (SELECT MAX(KR.LossDescription) FROM Kaimart_Rpt.ILRCL WHERE ClaimNumber = PA.ClaimNumber)
FROM
Policy_rpt.Ageny PA
UPDATE
PA
SET
LossDesc = (SELECT MAX(KR.LossDescription) FROM Kaimart_Rpt.ILRCL WHERE ClaimNumber = PA.ClaimNumber)
FROM
Policy_rpt.Ageny PA
ASKER
Hi reb,
Sorry for mistake in the Question...
There is only 2 Databases..Policy & Policy_Rpt.
There is no Kaimart & Kaimart_rpt...
Again i am pasting the criteria here, Please go through it & modify the solution for me .... Please....
LossDesc (ClaimNumber level one record for claim)
From Policy_Rpt.ILRCL.ClaimNumb
Use column LossDescription in Policy.LossDescription
* One value for all rows on a Claim
Sorry for mistake in the Question...
There is only 2 Databases..Policy & Policy_Rpt.
There is no Kaimart & Kaimart_rpt...
Again i am pasting the criteria here, Please go through it & modify the solution for me .... Please....
LossDesc (ClaimNumber level one record for claim)
From Policy_Rpt.ILRCL.ClaimNumb
Use column LossDescription in Policy.LossDescription
* One value for all rows on a Claim
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Iam sorry reb,
If i try to insert, Iam getting following errors...........
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "KA.ClaimNumber" could not be bound.
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
If i try to insert, Iam getting following errors...........
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "KA.ClaimNumber" could not be bound.
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
INSERT Policy_rpt.Ageny
SELECT I.*
,LossDesc = (SELECT MAX(KR.LossDescription) FROM Kaimart_Rpt.ILRCL WHERE ClaimNumber = PR.ClaimNumber)
FROM
Policy_Rpt.ILRCL PR
For updating NULL values for existing records use -
UPDATE
Policy_rpt.Ageny
SET
LossDesc = (SELECT MAX(KR.LossDescription) FROM Kaimart_Rpt.ILRCL WHERE ClaimNumber = PR.ClaimNumber)