Sql - Query to Insert a Column Value If It Does Not Exist in That Column

Insert column if it doesn't exist

Try this:

IF (SELECT COUNT(*) FROM syscolumns WHERE name = 'HasAccess24_7'
AND OBJECT_NAME(object_id) = 'tSafeUnit') = 0
BEGIN
    ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 tinyint not null default 0
END

INSERT COMMAND :: ERROR: column value does not exist

Character constants need single quotes.

Use: INSERT INTO users(user_name, name, password,email) VALUES ('user2','first last','password1', 'user2@gmail.com' );

See the manual: postgresql.org/docs/current/static/…

Note: After I encountered the same problem and almost missed the answer that exists in this page (at the comments section), thanks to @a-horse-with-no-name - I've posted this answer

Add a column to a table, if it does not already exist

You can use a similar construct by using the sys.columns table io sys.objects.

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)

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.

INSERT into a table from SELECT only if value doesn't exist

A unique/index constraint guarantees the uniqueness of values. So, it is recommended.

Unfortunately, though, a constraint violation causes the entire insert to fail. So, you can do:

insert into table2(t1col) 
    select id
    from table1 t1
    where not exists (select 1 from table2 t2 where t2.t1col = t1.id);

You should also have a unique index/constraint to prevent problems in the future.

insert row if one with equal value does not already exist

The correct syntax is:

INSERT INTO zyprexa (col1, col2, col3, col4, col5) 
SELECT NULL, 'hello', 0, 0, 0 
WHERE NOT EXISTS (SELECT 1 FROM zyprexa WHERE date = 'hello');

where col1, col2, col3, col4, col5 are the names of the columns that will receive the values (for example col2 should be date).

If you want the column date to be unique in your table, you should define it as such:

CREATE TABLE zyprexa  (
......
date TEXT UNIQUE,
......
);

and then you can execute INSERT statements with INSERT OR IGNORE, in which case if you try to insert a new row with a date (or any other column defined as unique) that already exists in the table, the insertion will fail (without any error):

INSERT OR IGNORE INTO zyprexa (col1, col2, col3, col4, col5) 
VALUES (NULL, 'hello', 0, 0, 0);

---

Related Topics