[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2585
  • Last Modified:

Insert into if not exists

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
bsunde
Asked:
bsunde
1 Solution
 
AdamSenior DeveloperCommented:
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
 
McOzCommented:
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
 
bsundeAuthor Commented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
8080_DiverCommented:
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
 
AdamSenior DeveloperCommented:
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
 
bsundeAuthor Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
AdamSenior DeveloperCommented:
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
 
AdamSenior DeveloperCommented:
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
 
bsundeAuthor Commented:
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
 
AdamSenior DeveloperCommented:
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
 
bsundeAuthor Commented:
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
 
AdamSenior DeveloperCommented:
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
 
bsundeAuthor Commented:
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
 
AdamSenior DeveloperCommented:
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
 
bsundeAuthor Commented:
Thank you so much! It worked perfectly.
0
 
AdamSenior DeveloperCommented:
Woo Hoo!

It took some time, but we got there!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now