.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Record Count on MERGE Statement on Insert,Update,Delete

Posted By:      Posted Date: November 25, 2010    Points: 0   Category :Sql Server
 

HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

Thanks.

 


khrizz tell


View Complete Post


More Related Resource Links

Is this a bug in MERGE statement with DELETE/INSERT?

  
This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

  
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?



Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.



For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Allow or deny roles to update, insert and delete records.

  
Hello, I have two roles "Admin" and "Basic". I also have a listview on the web page. My goal is that to make "Admin" role has the highest privilege to deal with records such as "insert", "update" and "delete". For the role "Basic", it only can update the records. Is there any sample code for that? Thanks.  

MS Linq To SQL Samples - Insert/Update/Delete

  
Hi, I'm stumbling along trying my hand at Linq to SQL and have come across this code below that was provided by MS.http://msdn.microsoft.com/en-us/vbasic/bb737928.aspxThe problem is there is no ".add" method for Categories to chose in the statement db2.Categories.add(newCategory)I've seen similar videos here on ASP .NET that uses code very much like the example below, but I am unable to run this example connected to a Northwind SQL database using Web Developer 2010 Express. This is the One-to-Many example listed under Linq To SQL Samples - Insert/Update/Delete. Thanks for any help! Shawn Dim db2 = New DataClassesDataContext Dim ds = New DataLoadOptions() ds.LoadWith(Of Category)(Function(p) p.Products) db2.LoadOptions = ds Dim newCategory = New Category With {.CategoryName = "Widgets", _ .Description = "Widgets are the customer-facing analogues " & _ "to sprockets and cogs.", _ .Picture = Nothing} Dim newProduct = New Product With {.ProductName = "Blue Widget", _ .UnitPrice = 34.56#, _ .Category = newCategory, _

Update, delete, insert

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases. How can I make the second database update, delete and insert?  

Update, Delete, Insert Options

  
I have two databases.  When I am using MS Visual Web Developer, I can drag the table onto the webpage and it gives me check boxes to Insert, Update, Delete,... When I do the exact thing using the other database, it only has check boxes forpaging, sorting and selection.  What is the difference?  I seem to have all teh same permissions in both databases.

Trigger without UPdate,INSERT and DELETE

  

I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

Is it possible to write a trigger for that? 

I am new to SQL Server and trigger.

Thanks,

MK 

 


record count for subquery which is used for INSERT

  
My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

Insert, Update, Delete with ajax

  

I'm kind of new to ajax. I normally just use  the canlendar and some popup stuffs.

i'm wondering what is the practice to doing Insert, Update, and Delete on  GridView/FormView with ajax.

Is UpdatePanel the best thing i can use it with Insert, Update, and Delete?


How do I control using ListView when to show the Update/Delete/Insert buttons and when not?

  

It should be simple, but I didn't found yet how to do it.

There is only one user (Admin with user name and passowrd, he has a cookie so I know when he is watching the page) I would like to show him the Update/Delete/Insert button options in my ListView, but to the other users I don't want them to be able doing this kind of things, So it should be hidden from them.

How I am doing it in C# code?

Showing/Hiding these buttons?

 


Insert, Update and delete locks

  

Hi,

I looking for the best way to set locks for the following sql commands:

1)

"

Insert

 

into Users

(

UserName

,

Email

,

Pswrd

 

)

values<

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.

  

Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )


Help required regarding audit (Update/INSERT/DELETE) a table in Database.

  

Hi all

 

Help required regarding audit (Update/INSERT/DELETE) a table in Database.

 

 

I have table having with imp Data lets say ID, some of users are setting ID's to their own values with out informing us. I know it is not possible using Application what every my Database using.

 

Now I want find that who is doing this

 

Insert,Delete Update Operations with XML

  

i already bind my grid with xml file using dataset as dataset has property of reading xml

but now i want to perform operations on xml like Inserting of Data,Deleting of Data and Updating can anyone give me short tutoral how we can perform this thankx you

my xml

<?xml version="1.0" encoding="utf-8" ?>
<Employees>
  <Employee>
    <Id>1</Id>
    <Name>Rockyy</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>2</Id>
    <Name>Slow</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>3</Id>
    <Name>Pop</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>4</Id>
    <Name>Music</Name>
    <Sal>1000</Sal>
  </Employee>
  <Employee>
    <Id>5</Id>
    <Name>Metal</Name>
    <Sal>2000</Sal>
  </Employee>
</Employees>


Loading with Gridview

  private void loadxml()
    {
        ds.Reset();
        ds.ReadXml(Server.MapPath("testing.xml"));
        GridView1.DataSource=ds;
        GridView1.DataBind();
        //throw new NotImplementedException();
    }


SELECT INSERT DELETE UPDATE happening at the same time

  

Hi All,
I have three table and SELECT DELETE INSERT UPDATE happening at the same time.
Join query is used for SELECT and DELETE.
INSERT and UPDATE no join query.
What are the actions to be taken to avoid deadlock. Will indexing help to resolve the dead lock.
Appreciate somebodys help immediately.

Regards,
Smithesh

 


Single insert/delete takes more than 5 sec on merge replicated table.

  

Dear experts,

Environment: MSSQL standard edition – 2008 / Windows server 2008 (64 bit)

I have lots of tables with merge publication (filtered) on windows 2008 server and it takes few seconds to insert/ delete single row on that database.

 

This is what execution plan shows.

Delete single row: 7 sec. It shows 47 queries.

Insert single row (2 columns): 4 sec. 35 queries.

 

What I can see it MSmerge_past_partition_mappings and MSmerge_current_partition_mappings table take 15%-30% of cost.

 

Please help to to resole this issue. I can send execution plan if anyone interested. 

select ,insert,update and delete is not getting recorded in the database audit spec.

  

Hi,

I am using SQLSERVER 2008 Standard Edition and i m trying to implement database audit for few database. Following is the script that i used:

CREATE

SERVER AUDIT [audit_mirror]

TO

SECURITY_LOG

WITH

(

QUEUE_DELAY = 1000

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend