Do Conditional Insert with SQL

Do conditional INSERT with SQL?

You can do that with a single statement and a subquery in nearly all relational databases.

INSERT INTO targetTable(field1) 
SELECT field1
FROM myTable
WHERE NOT(field1 IN (SELECT field1 FROM targetTable))

Certain relational databases have improved syntax for the above, since what you describe is a fairly common task. SQL Server has a MERGE syntax with all kinds of options, and MySQL has optional INSERT OR IGNORE syntax.

Edit: SmallSQL's documentation is fairly sparse as to which parts of the SQL standard it implements. It may not implement subqueries, and as such you may be unable to follow the advice above, or anywhere else, if you need to stick with SmallSQL.

SQL Conditional INSERT

You can try something like this:

insert into fruit (color_id, name)
select 11, 'banana'
where (select count(*) from fruit join colors on fruit.color_id = colors.id
where colors.id = 11 and colors.owner = 6) >= 1;

or equivalently

insert into fruit (color_id, name)
select 11, 'banana'
where exists 
(select * from fruit join colors on fruit.color_id = colors.id
where colors.id = 11 and colors.owner = 6);

SQL Server : INSERT with a condition

Try using if not exists

IF NOT EXISTS(SELECT * FROM [Files-Favorites] WHERE fileID = '1' AND auditorID = '34') 
BEGIN 
    INSERT INTO [Files-Favorites](fileID, auditorID) 
    VALUES('1', '34') 
END

Trigger for conditional insert into table

The trigger functions are executed for each row and you must use a record type variable called "NEW" which is automatically created by the database in the trigger functions. "NEW" gets only inserted records. For example, I want to insert data to users_log table when inserting records to users table.

CREATE OR REPLACE FUNCTION users_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    
    insert into users_log 
    (
        username, 
        first_name, 
        last_name
    )
    select 
        new.username, 
        new.first_name, 
        new.last_name;

    return new;
END;
$function$;

create trigger store_data_to_history_insert 
before insert
on users for each row execute function users_insert();

Stored procedure with conditional insert

You have no assignment statements for @StudentNumber in the first condition, You can try this:

CREATE PROCEDURE InsertIntoDefeatsGraduates
    @ComandmentNumber VARCHAR(100),
    @StudentFakNumber INT
AS
BEGIN
    DECLARE @StudentNumber INT 
    SELECT @StudentNumber = StudentNumber  FROM dbo.[Defeats-Graduates] 
               WHERE ComandmentNumber = @ComandmentNumber
    IF ISNULL(@StudentNumber, 0) > 0
      BEGIN
        SET @StudentNumber += 1

        INSERT INTO dbo.[Defeats-Graduates] (ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    ELSE
      BEGIN
        SET @StudentNumber = 1

        INSERT INTO dbo.[Defeats-Graduates](ComandmentNumber, StudentNumber, StudentFakNumber) 
        VALUES (@ComandmentNumber, @StudentNumber, @StudentFakNumber)
      END
    END

Conditional IF Exists Insert

The parentheses don't belong around the BEGIN/END:

IF NOT EXISTS (SELECT 1 FROM @Main WHERE CustomerId = @CustomerId)   
BEGIN  
    INSERT INTO @Result 
        SELECT .... FROM ... WHERE...
END
ELSE . . .

I would also suggest that you list the columns for @Result for the INSERT. This can prevent future problems.

Oracle - Conditional Insert with Sequence

SQL has no INSERT WHEN construct. It does have WHERE. So you intend something like this:

INSERT INTO TABLE1 (NAME, GROUP)
    SELECT 'DUPLICATE_NAME_TEST', 30
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.NAME = 'DUPLICATE_NAME_TEST');

sql command conditional insert

insert into your_table (objId, objCode)
select objId, 'KKK'
from your_table
group by objId
having sum(case when objCode = 'TE' then 1 end) > 0
   and sum(case when objCode = 'KKK' then 1 end) = 0

---

Related Topics