Why SQL INSERT INTO SELECT statement duplicates the rows?-CodePudding

I am using C# windows application and I created this stored procedures to save old result and new result but the procedure duplicates each row 2 times save one row for old result and one row for new result , I need to insert only one row for each result which is the old result only

this is the stored procedure code :

CREATE proc [dbo].[UPDATED_RESULTS]
@ORDER_ID int,
@TESTID int,
@APPROVED_BY varchar(50),
@APPROVED_DATE datetime, 
@RESULT_NUMBER varchar(50),
@MACHINE_ID int,
@patient_no int,
@custid int,
@CORRECTED_BY varchar(50),
@CORRECTED_DATE datetime,
@messageid int
AS
INSERT INTO [dbo].[LAB_RESULTS_UPDATED]
           ([ORDER_ID]
           ,[TESTID]
           ,[APPROVED_BY]
           ,[APPROVED_DATE]
           ,[RESULT_NUMBER]
           ,[machine_id]
           ,[patient_no]
           ,[custid]
           ,[CORRECTED_BY]
           ,[CORRECTED_DATE]
           ,[messageid])
     SELECT DISTINCT ORDER_ID,TESTID ,APPROVED_BY ,APPROVED_DATE ,RESULT_NUMBER ,MACHINE_ID ,patient_no,custid,@CORRECTED_BY,@CORRECTED_DATE,@messageid
     FROM LAB_RESULTS 
     WHERE ORDER_ID = @ORDER_ID 
     AND RESULT_NUMBER IS NOT NULL 
     AND RESULT_NUMBER != '' 
     EXCEPT SELECT [ORDER_ID],[TESTID],[APPROVED_BY],[APPROVED_DATE],[RESULT_NUMBER],[machine_id],[patient_no],[custid],[CORRECTED_BY]
           ,[CORRECTED_DATE]
           ,[messageid]
     FROM LAB_RESULTS_UPDATED

and this is the class code in C# application when click save button:

for (int i = 0; i < dgvResult.Rows.Count; i  )
{
    if (!String.IsNullOrEmpty(dgvResult.Rows[i].Cells[3].Value.ToString()))
    {
        result.UPDATED_RESULTS(
            Convert.ToInt32(txtOrder.Text),
            Convert.ToInt32(dgvResult.Rows[i].Cells[0].Value),
            txtApprovedby.Text,
            DateTime.Parse(dateTimeApprove.Value.ToString()),
            dgvResult.Rows[i].Cells[3].Value.ToString(),
            Convert.ToInt32(dgvResult.Rows[i].Cells[4].Value.ToString()),
            Convert.ToInt32(txtMRN.Text),
            Convert.ToInt32(textCustId.Text),
            txtApprovedby.Text,
            DateTime.Parse(dateTimeApprove.Value.ToString()),1);
        result.APPROVE_ALLLAB_RESULTS_NEW(
            Convert.ToInt32(txtOrder.Text),
            txtApprovedby.Text,
            DateTime.Parse(dateTimeApprove.Value.ToString()),
            Convert.ToInt32(dgvResult.Rows[i].Cells[0].Value),
            dgvResult.Rows[i].Cells[3].Value.ToString(),
            "No Report",
            Convert.ToInt32(dgvResult.Rows[i].Cells[4].Value.ToString()),
            Convert.ToInt32(txtMRN.Text),
            Convert.ToInt32(textCustId.Text),
            Convert.ToInt32(txtUpdateCount),
            txtExamUser.Text,
            DateTime.Parse(DateTimeExamined.Value.ToString()),6);
    }
}

How to update the stored procedure to save only one row the old result ?

CodePudding user response：

I believe this is what you want:

INSERT INTO [dbo].[LAB_RESULTS_UPDATED]
           ([ORDER_ID]
           ,[TESTID]
           ,[APPROVED_BY]
           ,[APPROVED_DATE]
           ,[RESULT_NUMBER]
           ,[machine_id]
           ,[patient_no]
           ,[custid]
           ,[CORRECTED_BY]
           ,[CORRECTED_DATE]
           ,[messageid])
     SELECT DISTINCT ORDER_ID,TESTID ,APPROVED_BY ,APPROVED_DATE ,RESULT_NUMBER ,MACHINE_ID ,patient_no,custid,@CORRECTED_BY,@CORRECTED_DATE,@messageid
     FROM LAB_RESULTS 
     LEFT JOIN (SELECT [ORDER_ID],[TESTID] FROM LAB_RESULTS_UPDATED) EX 
       ON LAB_RESULTS.ORDER_ID = EX.ORDER_ID AND LAB_RESULTS.TESTID = EX.TESTID
     WHERE ORDER_ID = @ORDER_ID 
     AND RESULT_NUMBER IS NOT NULL 
     AND RESULT_NUMBER != '' 
     AND EX.ORDER_ID IS NULL

Here I am doing a left join and only using items that are null -- this is the same as a "NOT EXISTS" but it allows you to join on two fields

NOTE: This assumes that there is only ever one of any combination of order_id and testid (which would be a standard relational design)

CodePudding user response：

NOT EXIST solved the duplicates , I added this line in where statement :

AND NOT EXISTS (SELECT TESTID FROM [LAB_RESULTS_UPDATED] WHERE ORDER_ID = @ORDER_ID)

and select distinct TESTID first instead of ORDER_ID and removed EXCEPT SELECT

this is the complete stored procedure :

ALTER proc [dbo].[UPDATED_RESULTS]
@ORDER_ID int,
@TESTID int,
@APPROVED_BY varchar(50),
@APPROVED_DATE datetime, 
@RESULT_NUMBER varchar(50),
@MACHINE_ID int,
@patient_no int,
@custid int,
@CORRECTED_BY varchar(50),
@CORRECTED_DATE datetime,
@messageid int
AS
INSERT INTO [dbo].[LAB_RESULTS_UPDATED]
           ([TESTID]
           ,[ORDER_ID]
           ,[APPROVED_BY]
           ,[APPROVED_DATE]
           ,[RESULT_NUMBER]
           ,[machine_id]
           ,[patient_no]
           ,[custid]
           ,[CORRECTED_BY]
           ,[CORRECTED_DATE]
           ,[messageid])
     SELECT DISTINCT TESTID , ORDER_ID ,APPROVED_BY ,APPROVED_DATE ,RESULT_NUMBER ,MACHINE_ID ,patient_no,custid,@CORRECTED_BY,@CORRECTED_DATE,@messageid
     FROM LAB_RESULTS 
     WHERE ORDER_ID = @ORDER_ID 
     AND RESULT_NUMBER IS NOT NULL 
     AND RESULT_NUMBER != '' 
     AND NOT EXISTS (SELECT TESTID FROM [LAB_RESULTS_UPDATED] WHERE ORDER_ID = @ORDER_ID)