CODEWITHSUNDEEP
sqlsql-server
jp2631
jp2631

Reputation: 39

Record Counts in Stored Procedures

I have a SP that INSERT INTO TBL_DOMAIN from TBL_STAGING, but first I want check table TBL_STAGING to make sure the table is not empty before I truncate table TBL_DOMAIN, if table TBL_STAGING got more than one record then proceed the truncate table TBL_DOMAIN then run the INSERT, ELSE message say the table TBL_STAGING is EMPTY and exit the SP. My goal is to make sure the table TBL_DOMAIN still have the data even is old. I'm very new SQL please help.

CREATE PROCEDURE [dbo].[SP_INSERT_ALL_DOMAIN]
WITH EXECUTE AS CALLER
AS
BEGIN      

 BEGIN TRANSACTION NT_ALL_DOMAIN 
    INSERT INTO  TBL_DOMAIN
        (DOMAIN_NAME,
        DISTINGUISHED_NAME,
        EMAIL_ADDR_I)
    SELECT  DOMAIN_NAME,  
        DISTINGUISHED_NAME,
        EMAIL_ADDR_I
    FROM TBL_STAGING

Upvotes: 0

Views: 58

Answers (1)

gonferr
gonferr

Reputation: 28

First you need to check if TBL_STAGING has data:

IF EXISTS (SELECT TOP 1 1 FROM TBL_STAGING)
BEGIN
  BEGIN TRANSACTION NT_ALL_DOMAIN 
  INSERT INTO  TBL_DOMAIN
      (DOMAIN_NAME,
      DISTINGUISHED_NAME,
      EMAIL_ADDR_I)
  SELECT  DOMAIN_NAME,  
      DISTINGUISHED_NAME,
      EMAIL_ADDR_I
  FROM TBL_STAGING
  COMMIT
END
ELSE
BEGIN
 RETURN 'no data on table'
END

Upvotes: 1

Related Questions