How to insert result of Store Procedure into sql table

Question

How to insert result of Store Procedure into sql table

akhterhussain80

Mr or Mrs. 500

Points: 504
More actions
January 8, 2019 at 9:59 am

#416609

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

You need to create a table with the same format columns as the stored procedure returns, then just insert into it from the EXEC:
INSERT INTO myTable(col1 ,col2,...) EXEC [dbo].[GetMachineAttendanceFinal5]

Sue_H SSC Guru Points: 90860 More actions · Answer 2

SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

Sue

akhterhussain80 Mr or Mrs. 500 Points: 504 More actions · Answer 3

Jonathan AC Roberts - Tuesday, January 8, 2019 10:27 AM
You need to create a table with the same format columns as the stored procedure returns, then just insert into it form the EXEC:
INSERT INTO myTable(col1 ,col2,...) EXEC [dbo].[GetMachineAttendanceFinal5]

Thanks Jonathan , how to avoid to duplication means that if i insert record in a table already then those record should not re insert in a table

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

akhterhussain80 - Tuesday, January 8, 2019 12:49 PM
Jonathan AC Roberts - Tuesday, January 8, 2019 10:27 AM
You need to create a table with the same format columns as the stored procedure returns, then just insert into it form the EXEC:
INSERT INTO myTable(col1 ,col2,...) EXEC [dbo].[GetMachineAttendanceFinal5]
Thanks Jonathan , how to avoid to duplication means that if i insert record in a table already then those record should not re insert in a table

You can't add a where to this type of insert. So it would probably be best to insert all the data into a temporary table then insert from the temporary table into the permanent table with a not exists

grother Grasshopper Points: 11 More actions · Answer 5

OR Just TRUNCATE the table before executing the statement