MS-SQL: Insert value only if value not present

INSERT INTO table1 (col_1)
SELECT '64.39.21.112'
WHERE NOT EXISTS (SELECT '64.39.21.112' FROM table1)

DECLARE @myValue varchar(20)
SET @myValue = '64.39.21.112'

IF NOT EXISTS (SELECT 1 FROM table1 WHERE col_1 = @myValue)
  INSERT INTO table1 (col_1) values (@myValue)
ELSE
  --whatever you want to do if the record exists already
  --No need for ELSE if you want to do nothing when the record exists

Alternatively, slightly alter jdlambert1's solution above as follows --

DECLARE @myValue varchar(20)
SET @myValue = '64.39.21.112'

INSERT INTO table1 (col_1)
SELECT @myValue
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE col_1 = @myValue)

you can use trigger, i have made it as DELETE from inserted just because i don't know what columns you want to validate


create INS_table1 on table1
INSTEAD OF INSERT
as
DELETE inserted
FROM (SELECT * FROM authors)

insert into table1
SELECT * FROM inserted

sorry, got the above wrong, trigger would be like

CREATE TRIGGER INS_table1 on table1
INSTEAD OF INSERT
as

INSERT INTO table1
SELECT * FROM inserted WHERE NOT EXISTS (SELECT * FROM table1 INNER JOIN inserted ON inserted.col1=table1.col1)

with this trigger you will always avoid inserting dublicated col1

The current code needs to be amended somehow. It inserts string values into the table. I use C# .NET.........

INSERT INTO table1 (col_1) VALUES ( ' " + str_col_1.Trim() + " ' ) ";

"IF NOT EXISTS... INSERT INTO..." is two separate commands. While it can work, it's better to use a single command:

If you have string variables named "IP" & "sql":

IP = "64.39.21.112"
sql = "INSERT INTO table1 (col_1) SELECT '" + IP + "'WHERE NOT EXISTS (SELECT '" + IP + "' FROM table1)"