两个在SQL Server中具有相同where子句的存储过程中插入到语句中 [英] Two insert into statement in a stored procedure with same where clause in SQL server

问题描述

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

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

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



我尝试过：



<预郎= SQL><预> CREATE <跨度类= 代码关键字> PROCEDURE [DBO] [sp_UpdateEmpPunchingDetailTbl]
<跨度。 class =code-keyword> 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

解决方案

我很惊讶，为什么你不使用Union Statement如

 CREATE PROCEDURE [DBO]。[sp_UpdateEmpPunchingDetailTbl] 
 AS 
 BEGIN 
 
 DECLARE @mydate DATETIME 
 
选择@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]之间进行转换（VARCHAR（25），DATEADD（DD， - （DAY（@mydate）+ 8），@mydate），101）
和CONVERT（VARCHAR（25） ，DATEADD（dd， - （DAY（@mydate） -  22），@ mydate），101）
 
  - 首先插入语句
 INSERT INTO Tbl_EmpPunchingDetail 
 SELECT Pin_Co德AS Pin_Code 
，P_Date AS P_Date 
，IN1 AS IN1 
，输出1的OUT1 
 FROM [Zultime] [DBO]。[TIME_SHEET] 
，其中P_Date NOT IN（
 SELECT P_Date 
 FROM Tbl_EmpPunchingDetail 
）
 AND P_Date< = dateadd（day，datediff（day，1，GETDATE（）），0）
 
 Union 
 SELECT [Pin_Code] 
，P_Date 
，'07：30'AS [IN1] 
，'16：30'AS [OUT1] 
 FROM [AttendanceCorrection] [DBO]。[Tbl_FMOEmp] 
 CROSS JOIN @P_Date 
，其中P_Date NOT IN（
选择P_Date 
起价Tbl_EmpPunchingDetail 
）
 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

解决方案

I am surprise, why you are not using Union Statement like

CREATE PROCEDURE [dbo].[sp_UpdateEmpPunchingDetailTbl]
AS
BEGIN

    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)

    --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)

 Union
 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子句的存储过程中插入到语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！