If Record Exists, Update It. If Not, Insert It

Question

2.00/5 (1 vote)

See more:

hi,
i want to insert data if it is not present in the table else update it
how to find the data already exists in the table without using storeprocedure
insert and update of data will be from a temp table

thanks inadvance

Posted 13-Feb-12 20:40pm

pradeep manne

Updated 15-Feb-12 23:58pm

v2

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Steve-3tc · Answer 1 · 2012-02-13T23:12:00

Hi There,

If you are using SQL 2008 you may want to look at the new Merge command.

MS Link:- http://technet.microsoft.com/en-us/library/bb510625.aspx

As per their site (technet.microsoft.com):-
Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Hope that helps...though the other answers are also very good. :)

Cheers,

Steve

BobJanova · Answer 2 · 2012-02-16T01:39:00

Solution 5

Depending your database provider, you can let the database deal with it.

SQL Server 2008+: MERGE (thanks Steve for finding this link already)
MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...

Posted 16-Feb-12 1:39am

BobJanova

Comments

_Zorro_ 16-Feb-12 10:24am

+5

Rishikesh_Singh · Answer 3 · 2012-02-16T04:13:00

Solution 6

Try using Command builder
http://msdn.microsoft.com/en-us/library/tf579hcz.aspx[^]

Posted 16-Feb-12 4:13am

Rishikesh_Singh

Aniket Yadav · Answer 4 · 2012-02-13T20:45:00

Solution 1

Its Simple.

First Fire A Query And Fetch The Data In The Datatable from the database for the Record You Are Looking for.

C#

if (mDT.Rows.Count > 0)
{
    //Fire UPDATE Query
}else
{
    //Fire INSERT Query
}

Posted 13-Feb-12 20:45pm

Aniket Yadav

Mehdi Gholam · Answer 5 · 2012-02-13T20:46:00

Solution 2

For better scalability and ease of programming it is better to :

1. Start a transaction.
2. Delete the record you want based on a primary key.
3. Insert your new data with a primary.
4. Rollback or Commit the transaction based on your flow logic.

This method ensures optimal database usage and minimum page locking for multi-user environments and is very easy to understand.

Posted 13-Feb-12 20:46pm

Mehdi Gholam

Comments

Mycroft Holmes 14-Feb-12 2:50am

While I agree with the transaction, recommending a delete/insert scenario for a newbie is very dangerous. He may not be aware of related records that may be left orphaned.

Mehdi Gholam 14-Feb-12 2:57am

Generally yes, although the RDBM will probably complain with foreign key constraints if they exist.

Point taken, thanks.

Varun Sareen · Answer 6 · 2012-02-13T20:46:00

Dear Friend,

First you have to select the count on the data by proper filter which is the differentiating factor from the other records like the new ID being generated or something else and then run the following query:-

select count([column (ID)]) from Table where....

now store the value into some integer and check that whether it is greater than zero or not. If yes then update it or else insert the data.

I hope this will help you out.

Thanks