Solved

Insert into if not exists

Posted on 2011-03-25
17
2,515 Views
Last Modified: 2012-05-11
I need to insert records into a table that may or may not have a record with this attributeID. If they do already have a record I want them left alone. If they do not have a record I want to add the record with this attributeID and value specified for every row in another table. I believe I have the insert statement correct, but I am not sure how to exclude the records I want left alone. Please advise.

Insert into CustomProgram (attributeID, ProgramID, value)
select '310', p.program, '650'
from Program p
0
Comment
Question by:bsunde
17 Comments
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35219053
Try this:

if not exists (select attributeID from CustomProgram where attributeID = 310) begin
    Insert into CustomProgram (attributeID, ProgramID, value)
    select '310', p.program, '650'
    from Program p
end

Open in new window


0
 
LVL 9

Expert Comment

by:McOz
ID: 35219089
Or in SQL alone:

Insert into CustomProgram (attributeID, ProgramID, value)
select '310', p.program, '650' from Program p
where not exists (select * from CustomProgram where CustomProgram.attributeID = '310')

Open in new window

Cheers
0
 

Author Comment

by:bsunde
ID: 35219143
Cyber-spy - I tried your command and it says it completed successfully but I don't see any records added

McOz- I tried your sql command it it returns no records.

Any other ideas?

Lines to the customprogram table are only added when someone adds a field in the front end application, but I need to populate all the current programs that are empty with a code of 650.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35219401
Or, using the SQL Server 2008 T-SQL, you can use the EXCEPT connector.  Don't have time to explain but look it up via Google ("SQL Server 2008 T-SQL EXCEPT").
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35219867
bsunde,

If you ran my command, and it returned without adding a record, can you confirm there is no record in CustomProgram where attributeID = 310? Try running:
select * from  CustomProgram where attributeID = 310

Open in new window

If it returns any results, then my script won't have inserted any records, which would be the correct behaviour.

Can you try again, changing both instances of 310 in the script with a number that absolutely does not already exist in the table.

Also, I just noticed that you enclosed the 310 in the insert statement in quotes, but I did not. If the AttributeID is a number, it should not be in quotes. If it is a string (like varchar) then both instances should be in single quotes.
0
 

Author Comment

by:bsunde
ID: 35219955
When I run the select command you gave me above it shows records for attributeID 310, but nothing for code 650. After the query is run again.

I ran the script changing 310 to another defined attribute but empty(312) and records did populate in the table.

I am sorry about the quotes on attributeid.  I did research and the field is an integer, so no quotes should be used.

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35221033
Well, in that example you will need to check for existance of 650, too.

If you add many records a more general approach would be to use an INSTEAD OF INSERT trigger, that can do the insert but also skip it, if the attributeID already exists.

Bye, Olaf.
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35221697
bsunde,

No need to apologise for the quotes - an easy mistake to make.

Anyway, did not your experiment with 312 show that my code works?

If not, what result did you expect?


0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35221704
Just noticed this line above:
but I need to populate all the current programs that are empty with a code of 650.

If you already have records in your CustomProgram table, where the 'value' column is empty, and which you want to update to set to 650, then use this:

update CustomProgram set value = 650 where value is null or value = 0

Open in new window


This will update all the records where the 'value' column is empty or contains a 0
0
 

Author Comment

by:bsunde
ID: 35224681
I am pretty new to Sql programming, so my wording is probably confusing you, so let me explain more.

Rows of data are only added into the custom program table when certain fields are changed in the front end application. There are not currently rows of data for most of the programs only a few which have a different code than 650. This is why I believe I need an insert into because the rows don't currently exist, but I need to keep the rows that are currently populated for attributeID 310.

the fields for the table are
customprogramID
attributeID
programID
value
date (doesn't need to be populated for this query)

Thanks for all your time. Hopefully this sheds some more light on the situation.
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35225685
OK, I think I get it.

There can be more than one row in the table with a particular attributeID (i.e. 310) but there can only be one row where attributedID = 310 and value = 650. i.e. there can only be one row with a particular combination of attributeID and value?

So, you can do something like this:

if not exists (select attributeID from CustomProgram where attributeID = 310 and value = 650) begin
    Insert into CustomProgram (attributeID, ProgramID, value)
    select '310', p.program, '650'
    from Program p
end

Open in new window


As you need to use the same values twice, you can use SQL variables:
declare @attributeID int
declare @value int
set @attributeID = 310
set @value = 650
if not exists (select attributeID from CustomProgram where attributeID = @attributeID and value = @value) begin
    Insert into CustomProgram (attributeID, ProgramID, value)
    select @attributeID, p.program, @value
    from Program p
end

Open in new window


More generally, using 'not exist' like this, in the brackets, use a query that will return no rows for whatever rules you define for the insert.

It doesn't really matter which field you select (in my example above it is attributeID) but don't use * as the server will have to return more values which is not efficient. As the value returned by the select statement is not actually used, pick a 'small' field - int is only 4 bytes, so makes a good canidate. varchar columns like the 'ProgramID' are usually longer, so aren't as good a choice.
0
 

Author Comment

by:bsunde
ID: 35232837
Sorry I didn't get back to you yesterday. Hopefully this clarification will help more.

In the customprogram table each programID should have only one record for each attributeID with one corresponding value. The front end database has a drop-down menu for the attributeID, so it couldn’t show if there were multiple values for the attributeID. I would say 90% of the programID’s (about 38000) don’t have a record for attributeID 310.

I just tried to run the top query taking out value = 650, since that would just add an additional line into the table and remove the population of data in the front end application. It said it completed successfully, but no data changed in the database.

I do appreciate all your time. Thanks for trying to help me figure this out.
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35252349
So, for each AttributeID (e.g. 310) there is one corresponding value (in this case, 650)

These pairs of values can be associated with any ProgramID, but only once
e.g. if ProgramID = 1, then it can have the AttributeID/Value pair of 310/650 associated with it, but only once.
The 310/650 pair can also be associated with ProgramID =2, 3 and so on

In this insert statement, a user selects an AttributeID from a list, you associate it with the 'value' and the you are trying to insert into the CustomProgram table all the ProgramID's along with the AttributeID/value pair.

So, if your original insert statement worked, you'd have an additional 34,200 rows (90% of 38,000) in the CustomProgram table, for all the ProgramID's that didn't already have an entry where AttributeID/Value = 310/650?

Important question - is your database server SQL Server 2005 or 2008?
0
 

Author Comment

by:bsunde
ID: 35282946
SQL Server 2008

For each programID they can have one value for attributeID 310. If the field is populated correctly it would be 34000+ entries of 650 and the other 10% of other values such as 300 and 700.

.

0
 
LVL 12

Accepted Solution

by:
Cyber-spy earned 500 total points
ID: 35287125
OK, the reason why I asked which type of server it is, is because SQL Server 2008 has a new 'MERGE' statement. See an intro at http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

MERGE CustomProgram cp
USING (SELECT Program from Program) as p
ON p.Program = cp.ProgramID AND cp.AttributeID = 310
WHEN NOT MATCHED  THEN
INSERT (attributeID, ProgramID, value) values ('310', p.program, '650')

Open in new window


I obviously haven't tested this, because I don't have your data, but it should work.
The URL above (or search Google for sql server 2008 merge statement) should give you enough to tweak it to suite.
0
 

Author Closing Comment

by:bsunde
ID: 35298630
Thank you so much! It worked perfectly.
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 35305336
Woo Hoo!

It took some time, but we got there!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now