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

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

Need help with MERGE statement.

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server




there a way to return the last CustId when INSERT but NOT in

View Complete Post

More Related Resource Links

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                              

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,

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

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

MERGE statement not working with replication


Hi all,

I am having problems using the MERGE statement in conjunction with replication (transactional with updatable subscriber - the problem only affect the subscriber).

As specified in the documentation:

For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.

Which can make sense but doesn't explain why when inserting a record using MERGE on a subscriber replicated table two replication triggers are fired one of which tries to insert NULL columns to the table failing replication.

I couldn't really find any documentation about that and the only thing left to do was removing all MERGE statements.

Does anyone have an idea on why it happens and has any suggestions?

Here our SQL vesion: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

Thanks a lot.


merge statement on DB2\linux view



I am using a merge statement to insert and update a view in DB2\linux view, for this we set up a linked server and for normal connections its working fine, but when I use merge statement in ssis its throwing the fallowing error

[Execute SQL Task] Error: Executing the query "SELECT [CUST_NBR]


      ,[CUST_CHRG_..." failed with the following error: "An unexpected token "DECLARE @tableVar TABLE 

      (MergeAction VARerface_1023_Archive)<concat>" was found following "".  Expected tokens may include: 

       "". SQLSTATE: 42601, SQLCODE: -104". Possible failure reasons: Problems with the query, "ResultSet"

        property not set correctly, parameters not set correctly, or connection not established correctly.


can you guys suggest me whether we can use merge statement on DB2\linux linked servers or not?

here the problem is with connection or what else?





how to merge two columns value into single column in sql select statement?


i have a query like this

SELECT     tblClientDocument_Base.Document_name, tblJobDocument_Base.Document_Name AS Document_name
FROM         tblClientDocument_Base JOIN
                      tblJobDocument_Base ON tblClientDocument_Base.Tenant_Id = tblJobDocument_Base.Tenant_Id

it returns

Document_name        Document_name

a                                   d

b                                   e

c                                    f

now i want to merge like this









plz help?

Passing Linq Statement between master and content page


I usually work with windows forms, and am just starting to work with ASP.Net, so appologies if this is a dumb question!

I have a master page which has a search button on it. The results of this are built up using a Linq statement and if there are any results, I want to display the results in a contents page.

On the masterpage, I have the following code:

Private _res

    Public Overridable ReadOnly Property SearchRes()
            Return _res
        End Get
    End Property

 Private Sub DoSearch()
   'some code to get a linq result

    If res.Any Then
            _res = res
            Server.Transfer("SearchResults.aspx", True)
            'no results
            LabelNoRes.Visible = True
        End If
 End Sub

On the SearchResults.aspx page I have this code.

Dim m As pxSite = CType(Page.Master, MasterPage)
        If m.SearchRes IsNot Nothing Then
            Label1.Text = "Results found = " & m.SearchRes.Count
        End If

m.searchRes is always nothing. 

What am I doing wrong here?


Mail Merge




I am after creating a mail merge documents from datasource and .dot template file on server side. Can you give me an idea how i will achive this?


Thanks in advance.



Syed Hussain



Merge SQL to Excel--Need .Net button to open on fly and have current SQL data!


           Hello all,
I do not know if this is the proper place to ask this, but my question is...
I have a SQL DB and I want to convert it to Excel Sheet (I did this using the wizard)!
In my aspx page I have a button.  So when I press this button I want the Excel sheet to open!  But I need the Excel sheet to show me the up to date information that is in the SQL DB.  

ReportViewer :: Failed to enable constraints. But my statement is valid!!

  • A
  • n error has occurred during report processing.
      • Exception has been thrown by the target of an invocation.
        • Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    Why do I keep getting this error, even when not using any data on the report. When I run the query in SQL server I get a resultset. However when I try to render the report I get this error.


    Whats wrong with this statement


    Im trying to updated some old ASP pages with some new .net pages and cant seem to get this update statement to work. I need to update the current ASP code below and then once thats working im going to use that same statement in a procedure for all our new .Net pages..

    So can anyone see what im doing wrong by inserting the 2nd column to the update statement?

    strSqual = "update tas set date_completed='" & TransDate & ", trans_id=" & InsertedTransID & "' where date_completed is null and sercontract_id="& strContractID & " and DATEDIFF(dd,task_due_date,'" & strProcDate & "')<=14"


    I am trying to add an extra column to the update, but not sure if the syntax is correct.

    If Statement Issue



    This is very strange to me. I have spent 2hrs looking into this inside out , but dont seem to get it

    I have an if statement that inserts data into the database. But i check to make sure the name is not entered twice. Now when the name is already in the db, then it should insert else put out a message saying its already in the db

    My code works fine except that  when its not in the db, its inserts but still puts out the message that says "Name is already in use" Not sure if am not seeing something. But thought i did bring it to the forum. Here is my code...Pretty simple

    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

            Dim objConnection As SqlConnection
            Dim objCommand As SqlCommand
            Dim strSQLQuery1 As String


            ' If there's nothing to Add for then don't search
            ' o/w build our SQL Query and execute it.
            If String.IsNullOrEmpty(txtAdd.Text) Or Leadbank.SelectedValue = "000" Then


    statement cannot appear outside of a method body


    hi all,

      i am posting my vb.net problem .plz tell me how to resolve this :-

    Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
        'Dim mysqlconnection As New SqlConnection(conn)
        'If mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();
        'Dim mySqlcommand As New SqlCommand("select CategoryId,CategoryName from Categories", mysqlconnection)
        'Dim mySqlDataAdapter As New SqlDataAdapter(mySqlcommand)
        'Dim mydataSet As New DataSet()

        'if mysqlconnection.State=ConnectionState.closed Then mysqlconnection.Open();

    i m getting 5 errors in this code

    declaration expected in mySqlDataAdapter.Fill method; Gridview1.DataSource=ds; Gridview1.DataBind();

    how to multiple condition in if statement in asp.net C#



    i am using if condition to find out a time

    if(time=="08 AM)


    response.write"shift A";


    but problem is how can i put multiple condition in if like

    if(time=="06 AM" and "08 AM" and "07 AM")



    Hello, i need help changing the following code into a switch...case statementUndecided

    <script runat="server">
    void Page_Load()
        if (Page.IsPostBack)
        public String toSring()
        int intcomputerChoice=1;
           if (Page.IsPostBack)

    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