Insert Record if no records found - SQL

Jeff S
Jeff S used Ask the Experts™
on
I need to insert a record if no records exist. In my example below, It returned no records; therefore I need it to return the current date for the created, for CreatedBy it can be  'N/A' and Description equal to 'No Correspondence Notes'. 

DECLARE @PATIENTVISITID INT = 695

SELECT
	  pc.Created
	, ISNULL ( NULLIF ( REPLACE ( pc.CreatedBy , CHAR ( 0 ) , '' ) , '' ) , 'System' ) AS CreatedBy 
	, CASE
		 WHEN pc.Description = '**long**' THEN pc.DescriptionLong
		 ELSE pc.Description
	  END AS Description
FROM PatientCorrespondence pc
WHERE pc.PatientVisitID = @PATIENTVISITID;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Mike EghtebasDatabase and Application Developer

Commented:
Is PatientVisitID PK? If so, then we need to add two more new lines to suppress identity effect.

Try:
DECLARE @PATIENTVISITID INT
As
Begin
DECLARE @Created dateTime = GEtDate();
DECLARE @CreatedBy varchar(50)= 'N/A';
DECLARE @Description varchar(50) ='No Correspondence Notes';
  
SELECT
	  pc.Created
	, ISNULL ( NULLIF ( REPLACE ( pc.CreatedBy , CHAR ( 0 ) , '' ) , '' ) , 'System' ) AS CreatedBy 
	, CASE
		 WHEN pc.Description = '**long**' THEN pc.DescriptionLong
		 ELSE pc.Description
	  END AS Description
FROM PatientCorrespondence pc
WHERE pc.PatientVisitID = @PATIENTVISITID;
)

if @@ROWCOUNT = 0
Begin

 Insert Into PatientCorrespondence (Created, CreatedBy, [Description], PatientVisitID) Values
                        (@Created, @CreatedBy, @Description, @PATIENTVISITID); 
End
End

Open in new window

Jeff S

Author

Commented:
Yes, PatientVisitId is the PK.
Database and Application Developer
Commented:
Lines 21 and 24 are added.
Try: 
DECLARE @PATIENTVISITID INT
As
Begin
DECLARE @Created dateTime = GEtDate();
DECLARE @CreatedBy varchar(50)= 'N/A';
DECLARE @Description varchar(50) ='No Correspondence Notes';
  
SELECT
	  pc.Created
	, ISNULL ( NULLIF ( REPLACE ( pc.CreatedBy , CHAR ( 0 ) , '' ) , '' ) , 'System' ) AS CreatedBy 
	, CASE
		 WHEN pc.Description = '**long**' THEN pc.DescriptionLong
		 ELSE pc.Description
	  END AS Description
FROM PatientCorrespondence pc
WHERE pc.PatientVisitID = @PATIENTVISITID;
)

if @@ROWCOUNT = 0
Begin
SET IDENTITY_INSERT PatientCorrespondence ON
 Insert Into PatientCorrespondence (Created, CreatedBy, [Description], PatientVisitID) Values
                        (@Created, @CreatedBy, @Description, @PATIENTVISITID); 
SET IDENTITY_INSERT PatientCorrespondence OFF
End
End

Open in new window

Jeff S

Author

Commented:
Many thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial