Check record before inserting into table

Question

Check record before inserting into table

Analyst_SQL 3,576

Below is query i want to make it check before inserting into table,if (PID is null or PID=1) in #Probale table ,then record Update in #Probale table otherwise doesn't and
then Insert query in #tbl_Packdetail will execute

--Insert

        IF NOT EXISTS 
(
   SELECT *
   FROM Probale
   WHERE   Prdno=10000001 and (PID is null or PID=1)
)
Begin

   Update   Probale  set PID=111  where Prdno=10000001


   Begin

   INSERT  INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(111,111,1,111,10000001)
   end
   end


   Create table #Proble (Prdno int ,PID int)
   Create table #tbl_PackDetail (PID int,Codeitem int,qty int, Orderno int,Prdno int )

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-06-05T08:55:53.553+00:00

I am not sure that I understand what your question is?

Analyst_SQL 3,576

i have two table #Proble and #tbl_PackDetail .
my requirement is that ,when Column value of PID in #Probale Table ,is null or equal to 1 ,then below query execute otherwise doesn't.

 Create table #Proble (Prdno int ,PID int)
     Create table #tbl_PackDetail (PID int,Codeitem int,qty int, Orderno int,Prdno int )

     Insert into #Proble values (10000001,Null)


  IF NOT EXISTS 
 (
    SELECT *
    FROM Probale
    WHERE   Prdno=10000001 and (PID is null or PID=1)
 )
 Begin

    Update   #Proble  set PID=111  where Prdno=10000001


    Begin

    INSERT  INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(111,111,1,111,10000001)
    end
    end

Ronen Ariely 15,206 Reputation points

2021-06-07T04:19:14.677+00:00

Good day,

is this a one time execution to update the table or do you need this condition for each new row that will be inserted to the table in the future?
Analyst_SQL 3,576 Reputation points

2021-06-07T06:43:16.14+00:00

for each row
Ronen Ariely 15,206 Reputation points

2021-06-07T07:26:02.377+00:00
OK, please provide the tools to recreate the issue

queries to create the relevant tables

queries to insert a few rows for start point

a simple insert query with a sample data which you want us to add the check to

the expected information in each table after the attempt to insert the new row repeat steps 3 and 4 for few samples of rows that you want to insert and end in different behavior

with this information it will be clear and simpler to solve the needs :-)

Accepted answer

1 additional answer

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-06-05T08:55:53.553+00:00

I am not sure that I understand what your question is?
Analyst_SQL 3,576 Reputation points

2021-06-05T09:13:39.737+00:00

i have two table #Proble and #tbl_PackDetail .
my requirement is that ,when Column value of PID in #Probale Table ,is null or equal to 1 ,then below query execute otherwise doesn't.

Create table #Proble (Prdno int ,PID int) Create table #tbl_PackDetail (PID int,Codeitem int,qty int, Orderno int,Prdno int ) Insert into #Proble values (10000001,Null) IF NOT EXISTS ( SELECT * FROM Probale WHERE Prdno=10000001 and (PID is null or PID=1) ) Begin Update #Proble set PID=111 where Prdno=10000001 Begin INSERT INTO tbl_PckDetail (PID,Codeitem,QTY,Orderno,Prdno) Values(111,111,1,111,10000001) end end
Ronen Ariely 15,206 Reputation points

2021-06-07T04:19:14.677+00:00

Good day,

is this a one time execution to update the table or do you need this condition for each new row that will be inserted to the table in the future?
Analyst_SQL 3,576 Reputation points

2021-06-07T06:43:16.14+00:00

for each row
Ronen Ariely 15,206 Reputation points

2021-06-07T07:26:02.377+00:00

OK, please provide the tools to recreate the issue

queries to create the relevant tables

queries to insert a few rows for start point

a simple insert query with a sample data which you want us to add the check to

the expected information in each table after the attempt to insert the new row repeat steps 3 and 4 for few samples of rows that you want to insert and end in different behavior

with this information it will be clear and simpler to solve the needs :-)

Answer 1

Hi @Analyst_SQL ,

According to your description, it seems that you only need to replace NOT EXISTS with EXISTS:

Create table #Proble (Prdno int ,PID int)  
Create table #tbl_PackDetail (PID int,Codeitem int,qty int, Orderno int,Prdno int )  
Insert into #Proble values (10000001,Null)  
Insert into #Proble values (10000001,1)  
Insert into #Proble values (10000002,1)  
      
  
SELECT * FROM #Proble    
SELECT * FROM #tbl_PackDetail   
  
  
IF EXISTS   
  (  
     SELECT *  
     FROM #Proble  
     WHERE   Prdno=10000001 and (PID is null or PID=1)  
  )  
BEGIN  
          
     UPDATE  #Proble    
  SET PID=111    
  WHERE Prdno=10000001  
          
     BEGIN  
          
     INSERT  INTO #tbl_PackDetail (PID,Codeitem,QTY,Orderno,Prdno)   
  VALUES(111,111,1,111,10000001)  
     END  
END

Output:

Regards
Echo

Answer 2

Please do not open duplicate questions!

I removed the duplicated question so people will be able to participate in the same discussion and not to have duplicate discussions

Note: redirect from one thread to other is not a good idea here since it leave us with the other thread in the list of question which seems like no one respond to => therefore people keep checking the thread in order to answer it but they redirected to the other thread while the original will stay without answer for ever

Share via

Check record before inserting into table

1 additional answer

Your answer