Insert into MSSQL database if teh record does not already exist

Posted on 2014-04-10
Last Modified: 2014-04-10

i have a .net application and i am trying to insert a value only if it does not already exist in teh table but every timei run it get get duplicated data

        sql.CommandText = "  insert into CC_eCollegeTermIDList   (TermCode,TermPart,eCollegeTermIDWebBased,eCollegeTermIDWebEnhanced,UpdatedUser,DateUpdated) Values " & " (@Term, @TermPart,'','','AutomatedSystem',@DateUpdate)" & _
 " Select @Term,@TermPart Where not exists(select * from CC_eCollegeTermIDList where TermPart = @TermPart and TermCode = @Term)"
Question by:Chris Jones
  • 2
  • 2
LVL 40

Accepted Solution

Sharath earned 500 total points
ID: 39992737
check this.
        sql.CommandText = "  insert into CC_eCollegeTermIDList (TermCode,TermPart,eCollegeTermIDWebBased,eCollegeTermIDWebEnhanced,UpdatedUser,DateUpdated) " & _
 " Select @Term,@TermPart,'','','AutomatedSystem',@DateUpdate Where not exists(select * from CC_eCollegeTermIDList where TermPart = @TermPart and TermCode = @Term)"

Open in new window


Author Comment

by:Chris Jones
ID: 39992759
oh wow thanks a million what was i doing wrong it almost looks the same ?
LVL 40

Assisted Solution

Sharath earned 500 total points
ID: 39992859
You have two SQL statements in your code. One INSERT with VALUES clause and another SELECT statement. So its just inserting the record and then running the SELECT statement.
What you need is one INSERT statement which checks if the values exist or not. I just removed the VALUES clause.

Author Closing Comment

by:Chris Jones
ID: 39993308
thanks one little thing can throw everything off

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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