solve the problem by using for loop or while loop

Question

2.00/5 (2 votes)

See more:

SQL

DECLARE @count int,
@index int,
@EMP_ID varchar(50),
@Emp_Name varchar(50),
@ID int
SET @index=0;

truncate table PercentageTable;                   
                   
 insert into empdetail(EMP_ID) SELECT (Emp_ID) from DetailedPerformanceMonthlyReport;
 insert into PercentageTable(EMP_ID,EMP_NAME) SELECT (Emp_ID,EMP_NAME)from DetailedPerformanceMonthlyReport;
 while(@index <@EMP_ID)
BEGIN
SET @index= @index+1;

INSERT INTO PercentageTable(PERCENTAGE) 
  (select CAST((Present/CAST(totdays AS DECIMAL(10,2)))*100 AS DECIMAL(5,2)) from DetailedPerformanceMonthlyReport); 
 (SELECT EMP_NAME from DetailedPerformanceMonthlyReport where EMP_NAME=Emp_Name));

 
 insert into PercentageTable(EMP_NAME) SELECT (Emp_Name)  from DetailedPerformanceMonthlyReport;
 
 END

ERRORS:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ';'.

empdetail consists of two columns ID, EMP_ID
percentagetable consists of three columns EMP_ID, EMP_NAME, PERCENTAGE

I want to push the three columns data in to percentage table but when iam trying to execute only one column is executing and remaining shows NULL in the table.

Can anyone help me to solve the problem by using for loop or while loop because iam a begineer to SQL.

Posted 2-Dec-13 17:52pm

Karthik Achari

Updated 2-Dec-13 20:05pm

v4

Add a Solution

Comments

skydger 3-Dec-13 1:25am

Which SQL engine do you use? Have you tried to debug your script step by step?

Karthik Achari 3-Dec-13 1:35am

Iam using MS SQLserver2008R2

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

skydger · Answer 1 · 2013-12-02T19:53:00

I'm not sure about varchar type of yor identifier column. You could try this code:

SQL

DECLARE @EMP_ID VARCHAR(50);
--SET @EMP_ID = ... -- Whatever you wish

INSERT INTO empdetail(EMP_ID)
SELECT MergeResult.Emp_ID
  FROM (
 MERGE PercentageTable AS Target
 USING (SELECT Emp_ID, Emp_Name,
               CAST((Present/CAST(CASE WHEN ISNULL(totdays, 0) = 0
                                       THEN -1
                                  ELSE totdays END AS DECIMAL(10,2)))*100 AS DECIMAL(5,2)) AS Percentage
          FROM DetailedPerformanceMonthlyReport
         WHERE Emp_ID <= @EMP_ID) AS Source
    ON Target.Emp_ID = Source.Emp_ID AND
       Target.Emp_Name = Source.Emp_Name
  WHEN MATCHED
       THEN UPDATE SET Percentage = Source.Percentage
  WHEN NOT MATCHED
       THEN INSERT (Emp_ID, Emp_Name, Percentage)
            VALUES (Source.Emp_ID, Source.Emp_Name, Source.Percentage)
            OUTPUT $action AS action, Inserted.*) AS MergeResult
 WHERE MergeResult.action = 'INSERT';

You could change -1 to any number to catch the division by zero exception

Karthik Achari · Answer 2 · 2013-12-02T23:32:00

SQL

truncate table empdetail;
truncate table percentagetable;
DECLARE @index int,
@Count int
Set @index=1;
Set @Count=0
begin
Insert into empdetail(Emp_Id,Emp_name) (Select Emp_id,Emp_Name from DetailedPerformanceMonthlyReport);
Select @Count=COUNT(ID) From empdetail;
Insert into PercentageTable (EMP_ID,EMP_NAME)(SELECT Emp_Id,Emp_name from empdetail);

while(@index<=@Count)
begin
Select @Emp_id=EMP_ID from empdetail where ID=@index;
UPDATE PercentageTable set PERCENTAGE=(SELECT CAST((Present/CAST(totdays AS DECIMAl))*100 AS DECIMAl) From DetailedPerformanceMonthlyReport where Emp_ID = @Emp_id)where Emp_ID = @Emp_id;
SET @index = @index+1;

solve the problem by using for loop or while loop

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0