Software

Ask something
Discussion Locked

Update and insert new rows in one query

0 helpful
4 replies

by maryweilage

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In this week's SQL Server e-newsletter, Arthur Fuller answers the question, "Can I update existing rows and insert (or delete) new ones in a single query?" Please let us know what you think about his answer and comments about the question.

If you aren't subscribed to the free SQL Server e-newsletter, you can automatically subscribe to this e-newsletter by pasting the following URL into your browser:
http://nl.com.com/MiniFormHandler?brand=builder &subs_channel=bldr_front_door&list_id=e046&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

4 replies

by technoontologist

From the OTN.oracle.com site:

Oracle9i Implementation:
In Oracle9i, the MERGE statement INSERTS and UPDATES the data with a single SQL statement.
MERGE INTO SALES_FACT D
USING SALES_JUL01 S
ON (D.TIME_ID = S.TIME_ID
AND D.STORE_ID = S.STORE_ID
AND D.REGION_ID = S.REGION_ID)
WHEN MATCHED THEN
UPDATE
SET d_parts = d_parts + s_parts,
d_sales_amt = d_sales_amt + s_sales_amt,
d_tax_amt = d_tax_amt + s_tax_amt,
d_discount = d_discount + s_discount
WHEN NOT MATCHED THEN
INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID, D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
VALUES(S.TIME_ID ,S.STORE_ID ,S.REGION_ID, S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);

by ntcse

The reason why people need to perform several statements within a single stored procedure are varied. Although "best practices" might discourage that in many cases, other factors come into play.

For example, I recently wrote a stored procedure to move data from the current production database to a new database with a new schema. I probably have several hundred statements in just a few stored procedures. Much simpler than creating 100 temporary stored procedures and then writing an app to call these.

Another issue is performance. I have re-designed many sites due to the fact that they were exhibiting sluggish performance. In some cases, this was due to too many round trips to the database when more could be accomplished with one call.

Yet another reason is to provide more encapsulation in your stored procedures. Some changes are very easy since you need only change the implementation of a given stored procedure, without having to change the other layers of code at all. Even if this might break your perfect vision of a 3 tier design, in the practical world, it may save time and money. If your business objects encapsulate the logic, why should the caller care how that logic is implemented?

So if you are using a RDBMS with advanced capabilities (SQL Server, Oracle), there are still some pros and cons to weigh before establishing the golden rule.

by pydss

I don't agree. It makes more sense to write economical, flexible and well commented code than it does to write lots of little, rigid bits.

Updating and inserting a record is modifying the table data and can usually, logically occur in one stored procedure whether working in an OOP environment or a process-driven one. Writing separate procedures is a good idea when there is a real need to distinguish between updating a record or adding a new record.

As to how to do it, the best way is to evaluate if the record already exists - if so update it, otherwise, insert a new record.

This discussion is closed to new replies.