How I can insert new record if record is null with this query

Question

1.00/5 (2 votes)

See more:

with cte as(
SELECT s.Id,s.ServiceName,u.ID as userid,u.FirstName FROM tblService s, tblUsers u 
)
, CTE2 AS(
SELECT C.UserID,c.Id,ServiceName,FirstName
,(Select SA.ServiceID from ServiceAuthorization SA WHERE SA.UserID=C.userid AND SA.ServiceID=C.Id) AS AllocateServiceId
  FROM cte C 
 )
Select * from CTE2 Order by userid

What I have tried:

with cte as(
SELECT s.Id,s.ServiceName,u.ID as userid,u.FirstName FROM tblService s, tblUsers u 
)
, CTE2 AS(
SELECT C.UserID,c.Id,ServiceName,FirstName
,(Select SA.ServiceID from ServiceAuthorization SA WHERE SA.UserID=C.userid AND SA.ServiceID=C.Id) AS AllocateServiceId
  FROM cte C 
 )
Select * from CTE2 Order by userid

   LEFT JOIN tblUsers u ON CTE2.userid = u.ID
   LEFT JOIN tblService s ON CTE2.Id=s.Id
   WHERE s.Id IS NULL
  BEGIN
     IF NOT EXISTS (Select SA.ServiceID from ServiceAuthorization SA WHERE SA.UserID=ID  AND SA.ServiceID=Id)
	 BEGIN
     PRINT 'RECORD EXISTS '
     END
  Else 
  BEGIN
     INSERT into ServiceAuthorization ([UserID], [ServiceID],[AddDate], [EditDate], [IsActive], [IsDisable], [CreatedBy]) VALUES (1, 1, CAST(N'2020-08-31T16:59:47.747' AS DateTime), CAST(N'2022-10-17T11:59:05.080' AS DateTime), 1, 0, 1)
  END
 END
GO

Posted 14-Feb-23 20:27pm

Pravin B Webplat

Updated 14-Feb-23 23:37pm

Add a Solution

Comments

CHill60 15-Feb-23 3:06am

What is wrong with the code you have?

Pravin B Webplat 15-Feb-23 4:21am

@Chilli60 new record not insert

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2023-02-14T23:37:00

That SQL doesn't even compile.

Let's start with

SQL

Select * from CTE2 Order by userid

   LEFT JOIN tblUsers u ON CTE2.userid = u.ID
   LEFT JOIN tblService s ON CTE2.Id=s.Id
   WHERE s.Id IS NULL

You can't put Order by before the JOIN definitions. Then you have some random code

SQL

BEGIN
     IF NOT EXISTS (Select SA.ServiceID from @ServiceAuthorization SA WHERE SA.UserID=ID  AND SA.ServiceID=Id)
	 BEGIN
     PRINT 'RECORD EXISTS '
     END
  Else 
  BEGIN
     INSERT into @ServiceAuthorization ([UserID], [ServiceID],[AddDate], [EditDate], [IsActive], [IsDisable], [CreatedBy]) VALUES (1, 1, CAST(N'2020-08-31T16:59:47.747' AS DateTime), CAST(N'2022-10-17T11:59:05.080' AS DateTime), 1, 0, 1)
  END
 END

which appears to have been copied from a stored procedure. You can't refer to the cte like that. Try inserting the output into a temporary file and use that for the Exists if you absolutely must - however ...

Do you really intend a cross-join in cte? That style of join is incredibly old-fashioned. One of the reasons it is not generally used is because of the danger of introducing cross-joins by forgetting to include appropriate WHERE clauses. CTE2 seems a little pointless and I really advise you avoid using correlated sub-queries like that - they are very bad for performance.

Better yet, work out what you want to do and write the code to do it. Most of this looks like it has been copied from multiple locations (different naming conventions on tables and different join styles for example). It is vastly overcomplicated for such a simple task.

If you need to come back for further help, please explain clearly what you are trying to achieve and give us your table schemas and some sample data.