Click here to Skip to main content
12,634,407 members (25,279 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
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 21:40pm
Updated 16-Feb-12 0:58am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
v5
Comments
_Zorro_ 16-Feb-12 10:24am
   
+5
Rate this: bad
 
good
Please Sign up or sign in to vote.

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 ...
  Permalink  
Comments
_Zorro_ 16-Feb-12 10:24am
   
+5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
if (mDT.Rows.Count > 0)
{
    //Fire UPDATE Query
}else
{
    //Fire INSERT Query
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  
Comments
pradeep manne 16-Feb-12 5:58am
   
insert and update of data will be from a temp table
Varun Sareen 16-Feb-12 11:42am
   
Then you can take the help of ADO.Net (DataSet and DataTable)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 16 Feb 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100