• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

MS-SQL: Insert value only if value not present

I'm trying to be more selective on my row inserts. Probably better than deleting duplicate rows later. So, I have the following syntax which is incorrect, but you'll get the idea I'm attempting.........

INSERT INTO table1 (col_1) VALUES ( '64.39.21.112' )
(select col_1, count(*) as counted
from table1
where col_1 value not exist)
0
kvnsdr
Asked:
kvnsdr
  • 2
  • 2
  • 2
  • +1
1 Solution
 
jdlambert1Commented:
INSERT INTO table1 (col_1)
SELECT '64.39.21.112'
WHERE NOT EXISTS (SELECT '64.39.21.112' FROM table1)
0
 
jchopdeCommented:
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)
0
 
FDzjubaCommented:
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FDzjubaCommented:
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
0
 
kvnsdrAuthor Commented:
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() + " ' ) ";
0
 
jchopdeCommented:
I do not claim to know C# at all but based on what your wrote above, use the following --

"IF NOT EXISTS (SELECT 1 FROM table1 WHERE col_1 = '" + str_col_1.Trim() + "')  INSERT INTO table1 (col_1) values ('" + str_col_1.Trim() + " ' ) ";
0
 
jdlambert1Commented:
"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)"
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now