两个在SQL Server中具有相同where子句的存储过程中插入到语句中

问题描述

我在单个存储过程中使用两个insert into语句，这个存储过程将自动执行，对于第一次插入到语句我得到所有行但是为了第二次插入到语句我接收零行因为条件

 其中 P_Date  NOT   IN （ SELECT  P_Date 来自 Tbl_EmpPunchingDetail）

任何人都可以指导我，我对于每个Pin_Code，每个日期只能获得一行而不重复执行多次执行存储过程以便第二次插入Statement ????或者我如何使用单个where子句插入声明？



我尝试过：



<预> CREATE PROCEDURE [DBO] [sp_UpdateEmpPunchingDetailTbl]
AS
BEGIN
- 首次插入语句
INSERT INTO Tbl_EmpPunchingDetail
SELECT Pin_Code AS Pin_Code
，P_Date AS P_Date
，IN1 AS IN1
，OUT1 AS OUT1
FROM [Zultime]。[dbo]。[TIME_SHEET]
WHERE P_Date NOT IN （
SELECT P_Date
FROM Tbl_EmpPunchingDetail
）
AND P_Date< = dateadd（day，datediff（ day， 1 ，GETDATE（））， 0 ）

- 第二次插入语句
DECLARE @ mydate DATETIME

SELECT @ mydate = GETDATE（）

DECLARE @ P_Date 表（[P_Date] DATE ）

我NSERT INTO @ P_Date
SELECT DISTINCT [P_Date]
FROM [Zultime]。[dbo]。 [TIME_SHEET]
WHERE [P_Date] BETWEEN CONVERT （ VARCHAR （ 25 ），DATEADD（dd， - （DAY（ @ mydate ）+ 8 ）， @ mydate ）， 101 ）
AND CONVERT （ VARCHAR （ 25 ），DATEADD（dd， - （DAY（ @mydate ） - 22 ）， @ mydate ）， 101 ）

INSERT INTO Tbl_EmpPunchingDetail
SELECT [Pin_Code]
，P_Date
，' 07:30' AS [IN1]
，' 16:30' AS [OUT1]
FROM [AttendanceCorrection]。[dbo]。[Tbl_FMOEmp]
CROSS JOIN @ P_Date
WHERE P_Date NOT IN （
SELECT P_Date
FROM Tbl_EmpPunchingDetai l
）
AND P_Date< = dateadd（day，datediff（day， 1 ，GETDATE（））， 0 ）
END

解决方案

i am using two insert into statement in single stored procedure, this stored procedure will execute automatically, For first insert into statement i am getting all rows but for second insert into statement i am receiving zero rows because of the condition "

where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail)

" Can anyone guide me that i should get only one row for each date for each Pin_Code without repeatation when executing stored procdeure multiple times for second insert into Statement???? or how i can use single where clause for both insert into statement ??

What I have tried:

<pre>CREATE PROCEDURE [dbo].[sp_UpdateEmpPunchingDetailTbl]
AS
BEGIN
    --First insert into statement
    INSERT INTO Tbl_EmpPunchingDetail
    SELECT Pin_Code AS Pin_Code
        ,P_Date AS P_Date
        ,IN1 AS IN1
        ,OUT1 AS OUT1
    FROM [Zultime].[dbo].[TIME_SHEET]
    WHERE P_Date NOT IN (
            SELECT P_Date
            FROM Tbl_EmpPunchingDetail
            )
        AND P_Date <= dateadd(day, datediff(day, 1, GETDATE()), 0)

    --Second insert into statement
    DECLARE @mydate DATETIME

    SELECT @mydate = GETDATE()

    DECLARE @P_Date TABLE ([P_Date] DATE)

    INSERT INTO @P_Date
    SELECT DISTINCT [P_Date]
    FROM [Zultime].[dbo].[TIME_SHEET]
    WHERE [P_Date] BETWEEN CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(@mydate) + 8), @mydate), 101) 
    AND CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(@mydate) - 22), @mydate), 101)

    INSERT INTO Tbl_EmpPunchingDetail
    SELECT [Pin_Code]
        ,P_Date
        ,'07:30' AS [IN1]
        ,'16:30' AS [OUT1]
    FROM [AttendanceCorrection].[dbo].[Tbl_FMOEmp]
    CROSS JOIN @P_Date
    WHERE P_Date NOT IN (
            SELECT P_Date
            FROM Tbl_EmpPunchingDetail
            )
        AND P_Date <= dateadd(day, datediff(day, 1, GETDATE()), 0)
END

解决方案

这篇关于两个在SQL Server中具有相同where子句的存储过程中插入到语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！