Solved

Insert into if not exists

Posted on 2011-03-25
17
2,525 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Server Management Studio, Save query 2 18
Loop to go backward 90 days 2 18
Attaching Database Failed ? 3 39
sql select record as one long string 21 22
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

785 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