CODEWITHSUNDEEP
sqlt-sql
Luis Valencia
Luis Valencia

Reputation: 34038

How to check if there are rows and execute an Insert

I got the following SQL Code, I need

  1. Execute an Insert only if the code returns more than 0 rows.

  2. Put messages on the screen for the person who executes the script, saying No missing rows were detected or 3 missing rows were detected and added.

    select * from DistributionKey_Section where SectionID not in ( select siteid from Site where SiteTypeCodeID IN(8) ) and DistributionKeyID NOT IN ( select DistributionKeyID from DistributionKey where UnitInclusive=1 )

Upvotes: 0

Views: 51

Answers (1)

Matt Klepeis
Matt Klepeis

Reputation: 1722

DECLARE
   @MissingRows int,
   @InsertedRows int    

SELECT *
FROM DistributionKey_Section
WHERE SectionID NOT IN ( select siteid from Site where SiteTypeCodeID IN(8) ) AND
      DistributionKeyID NOT IN ( SELECT DistributionKeyID FROM DistributionKey WHERE   
      UnitInclusive=1 )

SET @MissingRows = @@ROWCOUNT     

IF @MissingRows > 0
   BEGIN
   <Insert Statement/Logic>
   SET @InsertedRows = @@ROWCOUNT
   PRINT CAST(@InsertedRows as varchar(5)) + ' missing rows were detected and added'
   IF @MissingRows <> @InsertedRows 
      BEGIN
      RAISERROR('The number of rows inserted does not equal the number of rows missing', 16, 1)
      END
   END
ELSE
   PRINT 'No Missing Rows Detected'

Upvotes: 1

Related Questions