CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 47

Selectively insert rows based on if the row already exists or not

I currently have a table that has several thousand entries. I've found a way to insert of a new row of data for all entries in the system (see example below), but unfortunately, I just found out that some of the entries have the value already in there. I'm trying to figure out how to selectively run the INSERT INTO function based on if the row already exists or not.

Column 1   Column 2   Column 3 
10         Address    True 
10         City       False
10         State      True
20         Address    True
20         City       True
20         State      True
20         NEW        NEW*

Column 1   Column 2   Column 3
10         Address    True
10         City       False
10         State      True
10         NEW        NEW
20         Address    True
20         City       True
20         State      True
20         NEW        NEW

The code I've used for the insert is:

insert into table-name(Column1, Column2, Column3)
  select 
     Column1, 'NEW', 'NEW*' 
  from 
     table-name 
  group by 
     Column1

I'm assuming I need to use an IF/ELSE statement, but I'm running into issues on the IF statement. Currently, I'm using the following code:

IF Column 2 like 'New' leave NULL
ELSE 
  insert into table-name(Column1, Column2, Column3)
     select 
         Column1, 'NEW', 'NEW*' 
     from table-name 
     group by Column1

I know the IF statement is wrong, but trying to figure out what to do to leave the existing row values the same if they are already populated.

Upvotes: 1

Views: 2162

Answers (5)

Jeremy Hutchinson
Jeremy Hutchinson

Reputation: 2045

You can use a Merge statement to insert only rows that don't exist. As an added bonus you can update existing rows in the same statement if you need to do that.

http://msdn.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 0

Jason Whitish
Jason Whitish

Reputation: 1438

Using SQL Server, you can do a NOT EXISTS 

insert into table-name(Column1, Column2, Column3)
  select 
     Column1, 'NEW', 'NEW*' 
  from 
     table-name 
     WHERE NOT EXISTS (SELECT 1 FROM table-name xref WHERE xref.Column1 = table-name.Column1 AND xref.Column2 = 'NEW')
  group by 
     Column1

Upvotes: 1

Melanie
Melanie

Reputation: 3111

For SQL Server use a CTE to define your dataset to be inserted:

WITH NonDupes (<your fieldlist>)
AS
(SELECT <your fieldlist> FROM <your tables> WHERE <tablea>.<column> NOT IN (SELECT <column> FROM <tableb>))
INSERT INTO
<tableb>
VALUES(SELECT * FROM NonDupes)

Upvotes: 0

Brad
Brad

Reputation: 11515

I think what you want is an "INSERT INTO" with an "ON DUPLICATE KEY".

See: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Upvotes: 0

Gumflush
Gumflush

Reputation: 19

You can use the INSERT IGNORE INTO or INSERT … ON DUPLICATE KEY UPDATE syntax

see http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html and dev.mysql.com

Upvotes: 0

Related Questions