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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

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

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
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,

View Complete Post

More Related Resource Links

Record Count on MERGE Statement on Insert,Update,Delete


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? 



khrizz tell

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. 

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.

The DELETE statement conflicted with the REFERENCE constraint


I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

The statement has been terminated


INSERT Statement Inside a UDF?

I am using SQL Server 2005.  I know you can not put INSERT or UPDATE statements in a UDF.  I am looking for an alternative to getting the end result I need since this call has to go at a specific point in the code or it will never work.  I have a UDF which packs smaller boxes into a bigger box efficiently.  The UDF typically runs and creates as many bigger boxes as possible until all smaller boxes are used and then it returns a TABLE variable back to a stored procedure.  I need to insert a record into a new table at the point where each bigger box is full before it moves on to the next bigger box.  There is only a couple alternatives I can see but don't really know if each is possible.  I am open to an ideas: 1. Call a stored procedure from the UDF which executes the insert statement 2. Using EXECUTE to perform the insert statement 3. Re-write the UDF as a stored procedure.  It must be able to table two inputs and return a table which can be unioned with two other views. 4. Anything Else UPDATE: Scratch idea #1 since it says I can only call other functions or extended stored procedures UPDATE: Scratch idea #2 as it did not work

Large volume of INSERT/DELETE

 I support the DB for a 3rd party application. Lately users have been complaining about the application being sluggish. Checking into it I found that the vendor decided to write their own locking system (much like peoplesoft does/did). In short, I have a very small table (maxes out at around 3000 rows, 5 columns (int, varchar(30), int, varchar(30), int, int). They store locking records in the table, they are inserted and deleted at a rate of about 10 per sec/600-900 per min. The PK is composite across the columns required to enforce uniqueness. There is no identity column. The IO involved in these inserts and deletes results in enormous amounts of writelog waits. Since the application is built around this custom record locking, and this table is the record of who has what locked, delays on these inserts and deletes translates to delays on everything. I already have plans to address the disk subsystem. In the interim, is there anything I can do to relieve IO this table generates from a design perspective?

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

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.  

Insert Into SQL Server inside a For Each - Next Statement

I have the following code to For each Item in the file uploader, Insert Into database.... but It doesnt work in the For Each Statement but if i take it out of the For Each statement it works.   For Each item As AttachmentItem In Attachments1.Items            Dim objConn2 As New SqlConnection("Data Source=XXX")            objConn2.Open()            Dim objCmd2 As New SqlCommand("INSERT INTO clientport (name, event, date1, username, password, path)" & "VALUES (@name, @event, @date1, @username, @password, @path)", objConn2)                                 objCmd2.Parameters.AddWithValue("@name", clientname.Text)            objCmd2.Parameters.AddWithValue("@event", [event].Text)            objCmd2.Parameters.AddWithValue("@date1", [date].Text)            objCmd2.Parameters.AddWithValue("@username", username.Text)  &nbs

Merge Statement Error

I'm getting the error: Attempting to set a non-NULL-able column's value to NULL. And I can't figure why the error is occurring. Code for replication in SSMS using temp tables: Yes, it's somewhat redundant, it's all dummy tables mimicing our actual setup, this is one of several similar merge statements in an SP, however only this one throws any errors. As for testing: the first pass works fine, but on a second run of just the section marked for testing it will throw the above error when it hits the update section, specifically the phoneNumberOrder seems to be causing it. Interestingly, if you comment out that block and try rerunning, it will throw the same error but it now appears to stem from the INSERT statement's phoneNumberOrder block. Commenting that out will allow the statement to run just fine. The two problem sections are indicated by comments. Any insight is greatly appreciated. -------------------------------------------------Run Once CREATE TABLE #User_PhoneNumbers(     [phoneNumberID] [bigint] IDENTITY(1,1) NOT NULL,     [userID] [bigint] NOT NULL,     [phoneNumberOrder] [int] NOT NULL,     [countryCode] [nvarchar](6) NOT NULL,     [areaCode] [nvarchar](3) NOT NULL,     [localCode] [nvarchar](3) NOT NULL,     [lineNumber] [nvarchar](4) NOT NULL,   

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              


Does INSERTing a row equivalent to DELETE & INSERT? Please provide more info on this.

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.

Why single SQL delete statement will cause deadlock?


Hello everyone,

I am using SQL Server 2008 Enterprise. I am wondering why even a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time? Any tools or sample codes which could be used to reproduce this scenario?

For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column.

    create PROCEDURE [dbo].[FooProc]   
     @Param1 int
     ,@Param2 int 
     ,@Param3 int 
    DELETE FooTable WHERE  Param1 = @Param1    
    INSERT INTO FooTable   

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.




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