update record if it exists & insert if it does not

Question

5.00/5 (1 vote)

See more:

hi,

I have to use a SQL SERVER 2000 & form a query to update the record with new data if record exists, else insert it into the table.
Can it be done without using stored procedure.

Posted 1-Jun-10 6:43am

saloni15

Updated 1-Jun-10 9:58am

Deeksha Shenoy

v2

Add a Solution

Comments

radix3 2-Jun-10 0:50am

post in what u tried first

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

AspDotNetDev · Answer 1 · 2010-06-01T11:18:00

You can't perform a merge in SQL Server 2000 without multiple queries. If you are fine with multiple queries, then here are two approaches you could take.

Approach #1.

SQL

-- First query. INSERT INTO SomeTable (...) VALUES (...) WHERE NOT EXISTS (SELECT 0 FROM SomeTable WHERE ...) -- Second query. UPDATE SomeTable SET ...

Approach #2.

SQL

-- First query. SELECT CASE WHEN EXISTS (SELECT 0 FROM SomeTable WHERE ...) THEN 'update' ELSE 'insert' END -- Second query (if first returned "insert"). INSERT INTO SomeTable (...) VALUES (...) -- Second query (if first returned "update"). UPDATE SomeTable SET ...

There is also a method of cheating which technically satisfies your requirement, but is probably not what you are looking for. You could perform an insert into a secondary table. That table would have a trigger that would then perform the conditional logic to merge that data into the primary table. While a trigger is technically not a stored procedure, I don't think that's what you are looking for. On a similar note, you could actually put the trigger on the primary table. In that case, the trigger would delete the row if it already exists, which would mean the newly inserted data should work fine... that might cause some trouble with identity fields and such though (you might be able to resolve that by turning identity insert on). That way, you would just perform an insert and the old record would get deleted before the new one gets inserted (i.e., no update necessary).

Looks like it can be done. The credit for finding that out belongs to Andrew. Here is some sample code:

C#

SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand(@"IF EXISTS(SELECT 0 FROM SomeTable
WHERE ID = 0) BEGIN UPDATE SomeTable SET ID = ID + 1 END
ELSE BEGIN INSERT INTO SomeTable(ID) VALUES(0) END", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Sandeep Mewara · Answer 2 · 2010-06-01T08:34:00

saloni15 wrote:
form a query to update the record with new data if record exists else insert it into table. can it be done without using stored procedure..

It totally depends on the uniqueness you define for the records.

If its a simple uniqueness(just a field or so...) then a unique constraint might do for you....
but
If its a complex uniqueness(or an entire row data needs to matchup), it might be good to verify that in Businesslogic(if in code) or a SQL function (if in DB)...

So, without using stored proc, it can be done but would depend on your requirement!

update record if it exists & insert if it does not

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0