How result of store procedure insert into table in SQL?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

I have store Procedure, now i want to insert result of store procedure into table of EmployeeAttendance Col(AttID,EMPID,INTIME,OUTTIME,HOURS,DUTY,OT,OTAmount,Late

here is my store procedure....

What I have tried:

<pre>ALTER PROCEDURE [dbo].[GetMachineAttendanceFinal5]

AS

BEGIN

      SET NOCOUNT ON;

     
;WITH Dates (ReportingDate) 
AS (
   SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
   UNION ALL
   SELECT DATEADD(day, 1, d.ReportingDate)
   FROM Dates d
   WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))

,CTE As
    (
    select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
    )
    ,CTE4 As
    (
    Select MachineAttendance.EmpID,MachineAttendance.MDate,MachineAttendance.INOUT from MachineAttendance
    ) 
    
    ,cte1 AS
    (
    SELECT CTE4.EmpID, CAST(CTE4.MDate as Date) AS [Date], 
    CASE WHEN CTE4.INOUT = 1 THEN CTE4.MDate END AS INOUT_INTIME,
    CASE WHEN CTE4.INOUT = 2 THEN CTE4.MDate END AS INOUT_OUTTIME
    From 
     CTE4
    
    ), 
   cte2 
    as
    (
    select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME, 
                          MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY EmpID, [Date]
    )
    select cte.EmpID,d.ReportingDate,cte2.Date,cte2.INTIME,  cte2.OUTTIME,  cte2.[Hours]
    , CASE WHEN  cte2.[Hours] >= 8 THEN 1
    WHEN  cte2.[Hours] = 0 THEN 0
    WHEN  cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
    CASE WHEN  cte2.[Hours] > CTE.Dhour then  cte2.[Hours] - CTE.Dhour else 0 End as OT,
    CASE when   
    cte.OTEntitled = 'Yes'  AND cte2.[Hours] >= CTE.Dhour 
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,  

   
    Convert(varchar(10), cte2.INTIME,108) as [Time],
   Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late    
    from cte Cross Apply Dates d 
		Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
    order by cte.EmpID asc

Posted 8-Jan-19 5:19am

Member 12314309

Updated 8-Jan-19 6:17am

Add a Solution

Comments

OriginalGriff 8-Jan-19 11:29am

And?
You wrote the SP, you know what it does.
So what is the problem in modifying it to do a relatively trivial task?

Member 12314309 8-Jan-19 11:33am

i defined col above in attendance table,that is the result of this SP,this result i want to insert into a table of employeeattendance

OriginalGriff 8-Jan-19 11:37am

At the risk of repeating myself:
"And?
You wrote the SP, you know what it does.
So what is the problem in modifying it to do a relatively trivial task?"

Remember, we can't see your screen, we can't access your HDD, we can;t read your mind. We only get exactly what you type to work with.
So we have no idea why something is proving difficult, particularly for someone who can evidently write relatively complex SQl without any problems ... and if we don't know why it's giving you difficulty, we can't really answer a question you haven't asked!

Member 12314309 8-Jan-19 11:44am

Dear Original ,kindly do not take it on headache ,,,just i simply ask that how SP result which are coming in column to insert into table ,
I have attendance machine talbe in which employee INtime and OUttime is coming 1 and 2,from this INTIME and OUTTIME i calculated hours,OT,OTamount and Late,now these column result i want to insert into another table....

OriginalGriff 8-Jan-19 11:50am

I ran that through Google Translate which said
"I'm a student and this is my homework. I've found some code on the internet, but I don't understand it at all, and it doesn't do exactly what I need to hand it in to my tutor. Please do my homework for me."

Is that correct?

CHill60 8-Jan-19 12:16pm

LOL! I recognise bits of "his" code from one of my solutions and some of it came from MadMyche. This one is a real help vampire and making zero effort to understand.

Member 12314309 8-Jan-19 12:19pm

LOlz....i found solution here is ... INSERT INTO EmployeesAttendance
EXEC GetMachineAttendanceFinal5 ... but how to restrict that record which already exit in a table

ZurdoDev 8-Jan-19 11:31am

What he said ^

Member 12314309 8-Jan-19 11:34am

i said that SP Result ,means which value i calculated in SP ,i want to insert into Employee attendance table.

ZurdoDev 8-Jan-19 11:58am

But you wrote the Stored Procedure, right?

So, you can do anything you want in it.

You can SELECT * ...
and then INSERT INTO TABLE ...
and then anything else.

Member 12314309 8-Jan-19 12:21pm

Thanks for sharing ....another confusion is that if SP result is inserted then again same date record will insert ,so how to restrict duplication of record of same date for each employee

Member 12314309 8-Jan-19 12:22pm

INSERT INTO EmployeesAttendance
EXEC GetMachineAttendanceFinal5

ZurdoDev 8-Jan-19 12:39pm

That will work but you should put it in the stored procedure itself.

1 solution

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)

MadMyche · Accepted Answer · 2019-01-08T06:17:00

Your SP ends with a SELECT statement which returns the values to the calling application.
(EXACTLY the same as yesterdays post requesting this to update a table)

What you would need to do if you wanted this data to be inserted into your table would be to build an INSERT statement for the EmployeeAttendance table.
(as opposed to the UPDATE statement I wrote for you yesterday)

You are going to need to double check the mappings of these columns, did the best I could with what you had

SQL

INSERT EmployeeAttendance (EMPID, INTIME, OUTTIME, [HOURS], DUTY, OT, OTAmount, Late)  
select	cte.EmpID
	,	cte2.INTIME
	,	cte2.OUTTIME
	,	cte2.[Hours]
	,	CASE
			WHEN  cte2.[Hours] >= 8 THEN 1
			WHEN  cte2.[Hours] = 0 THEN 0
			WHEN  cte2.[Hours] >= 6 THEN 0.5
		END AS [Day]
	,	CASE
			WHEN  cte2.[Hours] > CTE.Dhour then  cte2.[Hours] - CTE.Dhour 
			else 0
		End as OT
	,	CASE
			when cte.OTEntitled = 'Yes'  AND cte2.[Hours] >= CTE.Dhour 	THEN (( cte2.[Hours] - 8) * 100)
				else 0
		END AS OTAMount
	,	Convert(varchar(10), cte2.INTIME,108) as [Time]
	,	Case
			When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1
			else 0
		end as Late    

from cte Cross Apply Dates d 
Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate

Please note
- ORDER BY was removed, is immaterial to this
- You have dates in the select portion, but not in EmployeeAttendanceTable
- No clue if DUTY mapped to Date
- The DATE type does not have a "format" to it. While you can include the code it does nothing