Insert into if not exists

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

Select allOpen in new window

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

Select allOpen in new window

Cheers

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.

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").

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

Select allOpen 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.

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.

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.

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?

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

Select allOpen in new window

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

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.

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

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

Select allOpen 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.

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.

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?

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.

.

Thank you so much! It worked perfectly.

Woo Hoo!

It took some time, but we got there!