SQL Server - two queries(Insert and update) in one stored procedure

HI...

If this needs to be done for each entry in the table, you can use a  trigger in this scenario after the insert, 

Hi,

Thanks for your reply..not for 10 rows i have to update company email id in the previously entered records...

you mean if new record insert into the database then you need to update the company email for previous record with the email you have entered.

Suppose you entered new record (ex 15th record) then which record company email id will be update ? 14th record's company email id ?

Tanisha,

A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach.  This will do a table/index scan for both the SELECT statement and the UPDATE statement.  In most standard approaches, the following statement will likely provide better performance.  It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.  Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take.  Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way

when i entered the company email id in the 15th(new record)..then i have to update all the previously entered company email id in the old records with this newly entered email id...

you can do this by first store/retrieve the value of comapny email id of newly entered row. Then you can execute Update query to update all the company email id of previously entered record by not setting any Where condition in Update query.

see the below sample code :

I assume you have some ID field in your table with set Identity/Autoincrement. So your ID will be generated automatically. Consider you have 14 record in your table and you are inserting 15th new record.

Insert Into yourtable(companyname, companyemail) Values ('abc Company', 'abc@gmail.com')

SET @CompanyEmail = (Select Top(1) companyemail From yourtable Order By ID Desc)

Update yourtable SET companyemail = @CompanyEmail

So it will update all the previously entered CompanyEmail id with new email id..

Hi,

So it is simple. As you will carry Email value to the Procedure for Inserting purpose, it will do the following:

(a) INSERT INTO <table> ...
(b) UPDATE <table> SET <email column> = <email value>

Above Update statement will update all rows including your new row added.