SQL Server If Not Exists Usage

SQL Server IF NOT EXISTS Usage?

Have you verified that there is in fact a row where Staff_Id = @PersonID? What you've posted works fine in a test script, assuming the row exists. If you comment out the insert statement, then the error is raised.

set nocount on

create table Timesheet_Hours (Staff_Id int, BookedHours int, Posted_Flag bit)

insert into Timesheet_Hours (Staff_Id, BookedHours, Posted_Flag) values (1, 5.5, 0)

declare @PersonID int
set @PersonID = 1

IF EXISTS    
    (
    SELECT 1    
    FROM Timesheet_Hours    
    WHERE Posted_Flag = 1    
        AND Staff_Id = @PersonID    
    )    
    BEGIN
        RAISERROR('Timesheets have already been posted!', 16, 1)
        ROLLBACK TRAN
    END
ELSE
    IF NOT EXISTS
        (
        SELECT 1
        FROM Timesheet_Hours
        WHERE Staff_Id = @PersonID
        )
        BEGIN
            RAISERROR('Default list has not been loaded!', 16, 1)
            ROLLBACK TRAN
        END
    ELSE
        print 'No problems here'

drop table Timesheet_Hours

When using IF NOT EXISTS(SELECT... in Sql Server, does it matter which columns you choose?

I think it was back in the 6.5 - 7 period of SQL Server that they made the query optimizer smart enough to know that:

IF NOT EXISTS(SELECT * FROM Countries WHERE Name = 'France')

Does not actually need to return any row data. The advice to use SELECT 1 pre-dates that, yet continues on as a myth.

Arguably, it's a fault with the SQL standard - they ought to allow EXISTS to start with the FROM clause and not have a SELECT portion at all.

---

And from Subqueries with EXISTS:

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

MS SQL query - if not exists a table

What you have to do is dynamically call the sysobjects table to get if the table exists and then conditionally choose what you want to insert into your temp table.

This probably the code you need:

CREATE TABLE #temp
(
      DB VARCHAR(50),
      Tab VARCHAR(50),
      [COUNT] INT 
) 
DECLARE @str NVARCHAR(1000) 
DECLARE @db_name NVARCHAR (150)
DECLARE @tab1 NVARCHAR (150)
DECLARE @count INT

set @tab1 = 'dbo.test'

DECLARE c_db_names CURSOR FOR

SELECT name 
FROM sys.databases
WHERE name like '%KNF%'

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN

        SET @str = N'Select @internalVariable = (select count(1) from ' + @db_name + '.dbo.sysobjects where name = ''' + replace(@tab1, 'dbo.','') + ''') '

        exec sp_executesql
            @str, --dynamic query
            N'@internalVariable int output', --query parameters
            @internalVariable = @count output --parameter mapping
  IF ( @count = 1 ) 
  BEGIN

      EXEC('
        INSERT INTO #temp
        SELECT ''' + @db_name + ''',''' + @tab1 + ''',COUNT(*) FROM ' + @db_name + '.' + @tab1 + '

      ')
  END
  ELSE 
  BEGIN
      EXEC('
        INSERT INTO #temp
        SELECT ''' + @db_name + ''',''no table'', null
      ')
  end

  FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

SELECT * FROM #temp

DROP TABLE #temp

SQL Server Insert if not exists

instead of below Code

BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

replace with

BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

Updated : (thanks to @Marc Durdin for pointing)

Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a transaction doesn't solve this.

How to use EXISTS and NOT EXISTS in one query?

The condition:

NOT EXISTS (SELECT * FROM Table3)

...is always false if there are any rows in Table3, so your query returns no rows.

You need to add a predicate to show how Table3 rows are qualified, as shown in bold below:

SELECT Table1.Id
FROM Table1 AS Table1
WHERE EXISTS
    (
        SELECT 1 
        FROM Table2 AS Table2
        WHERE
            Table1.DemoID  = Table2.DemoID 
            AND Table2.IsTrue= 1
    )
    AND NOT EXISTS
    (
        SELECT 1
        FROM Table3
        WHERE
            Table3.Table1_ID = Table1.Id -- New
    );

Demo: db<>fiddle

SQL Server : multiple WHERE NOT EXISTS clauses

Your EXISTS clause excludes all lines where any one of the conditions is TRUE, not only lines where all conditions are TRUE. Try this:

INSERT INTO Load_Charges

SELECT *

FROM Load_Charges_IMPORT

WHERE NOT EXISTS (
    SELECT * 
    FROM Load_Charges 
    WHERE Load_Charges_IMPORT.[Load ID]=Load_Charges.[Load ID]
    AND Load_Charges_IMPORT.[Charge Description]=Load_Charges.[Charge Description]
    AND Load_Charges_IMPORT.[Charged Amount]=Load_Charges.[Charged Amount]);

Understanding NOT EXISTS in SQL

So, the original problem was to:

Retrieve the names of each employee who works on ALL the projects controlled by department 5.

The provided answer makes use of the equivalence of:

1. All x such that f(x)
2. No x such that not f(x)

To put that in English, the problem is equivalent to finding those employees for whom there is no project controlled by department 5 that the employee doesn't work on.

So, first find all the projects controlled by department 5, then remove from that any project that the employee works on. That's exactly what the provided answer is doing. If there is nothing left, then there is no project controlled by department 5 that the employee doesn't work on. So by the equivalance, the employee works on all the projects controlled by that department.

While this is technically correct, it can feel a little odd. Especially if it were the case that department 5 controls zero projects. If that were true, the query would return all the employees ... which might not be quite what was expected.

---

Related Topics