Hot answers tagged

105

The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data: DECLARE @CategoryItem AS TABLE ( CategoryId integer NOT NULL, ...


46

You can try this IF EXISTS(select * from test where id=30122) update test set name='john' where id=3012 ELSE insert into test(name) values('john'); Other approach for better performance is update test set name='john' where id=3012 IF @@ROWCOUNT=0 insert into test(name) values('john'); and also read this bad habits to kick on schema prefix


31

There would not be a problem if the table variable only ever held one value. With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company. Process A reads the destination, finds no row, and inserts the value '1'. It holds an exclusive ...


20

This is a bug. It is related to MERGE-specific hole-filling optimizations used to avoid explicit Halloween Protection and to eliminate a join, and how these interact with other update plan features. There are details about those optimizations in my article, The Halloween Problem – Part 3. The giveaway is the Insert followed by a Merge on the same table: ...


19

There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help. There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there: MERGE @Emp emp USING @EmpUpdates eup ON emp.empid = eup.empid WHEN MATCHED AND emp.empaddress <> eup.empaddress ...


17

Personally, I don't like MERGE because there are many unresolved bugs: This blog post (scroll down to "Other MERGE issues") This answer by AlexKuznetsov These bugs on Connect These posts on Paul White's blog And this list may not even be exhaustive - given the number of bugs discovered by this small set of users, I have to wonder how many haven't been ...


17

Assuming SQL Server 2008 or later, you could use MERGE: Table CREATE TABLE dbo.Test ( id integer NOT NULL, name varchar(30) NULL, CONSTRAINT PK_dbo_Test__id PRIMARY KEY CLUSTERED (id) ); Query MERGE dbo.Test WITH (SERIALIZABLE) AS T USING (VALUES (3012, 'john')) AS U (id, name) ON U.id = T.id WHEN MATCHED THEN UPDATE SET T....


12

You need to give the query processor a more efficient access path to locate StudentTotalMarks records. As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId] applied to each row: The engine takes U (update) locks when reading as a basic defence against a common cause of conversion deadlocks. This ...


12

OK, after looking everything over a couple of times, I think that your basic assumption was correct. What's probably going on here is that: The MATCH part of the MERGE checks the index for matches, read-locking those rows/pages as it goes. When it has a row without a match, it will try to insert the new Index Row first so it will request a row/page write-...


11

When you update a row, it will appear in both the inserted (post-update value) and deleted (pre-update value) pseudo-tables. If this seems odd, consider that an update is logically a deletion followed by an insert (though the update may not be physically performed that way). When used with MERGE, the OUTPUT clause can include an extra column named $action. ...


10

Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place. First, thank you for the reproduction script. The problem is not that SQL Server cannot insert or update a particular user-visible row. As ...


10

PROBLEM NUMERO ONE GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a line by itself (optionally followed by only an INT value to cause repetition of that batch) as it is parsed by these client utilities to indicate where to split ...


9

You can filter out the rows you need to consider from the target table in a CTE and use the CTE as the target in the merge. WITH T AS ( SELECT M.LeftId, M.RightId, M.CustomValue FROM @Mappings AS M WHERE EXISTS (SELECT * FROM @Values AS V WHERE M.LeftId = V.LeftId) ) MERGE T USING @Values AS S ON T....


9

I would venture that your best approach is to use separate statements for each of the potential actions, and put them in a serializable transaction. You get to use tried and true statements with no funny semantics or "best practices" violations, and you get to avoid all of the problems I outline in this post, including wrong results bugs and potential index ...


8

I think SQL_Kiwi provided a very good analysis. If you need to solve the problem in the database, you should follow his suggestion. Of course you need to retest that it still works for you every time you upgrade, apply a service pack, or add/change an index or an indexed view. There are three other alternatives: You can serialize your inserts so that they ...


8

Because you are using a Sequence, you can use the same NEXT VALUE FOR function -- that you already have in a Default Constraint on the Id Primary Key field -- to generate a new Id value ahead of time. Generating the value first means that you don't need to worry about not having SCOPE_IDENTITY, which then means that you don't need either the OUTPUT clause or ...


8

Is placing IX followed by X on the object eligible? Is it bug or not? It looks a bit odd, but it is valid. At the time the IX is taken, the intention may well be to take X locks at a lower level. There's nothing to say that such locks must actually be taken. After all, there might not be anything to lock at the lower level; the engine cannot know that ahead ...


8

There is always Sixth, or Domain Key, Normal Form. Here each non-key column becomes it own table. So 3NF table T(Key, Col1, Col2, ..) becomes T1(Key, Col1), T2(Key, Col2) etc. Those new tables which require uniqueness can have it declared. I think having multiple unique constraints on a table is perfectly OK, however. Take for example a table of countries. ...


7

A MERGE would be more efficient since you're only executing the query once. Even better would be to do a set-based MERGE rather than doing a bunch of single-row MERGE statements which is what I'm assuming you'd be implementing given the alternative of doing a SELECT to see if the row exists. Rather than doing a SELECT and then determining whether to do an ...


7

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this. Pick a value greater than your current ident values on either DB. I would pick a round value, say 1,000,000. Pick the ident values you want to change (for example if you have lookup tables that ...


7

I would recommend simply avoiding MERGE in the first place for all of the reasons listed here and just simply use a standard update statement. UPDATE A SET Col1 = b.Col1 + b.Adjustment ,Col2 = b.Col2 + b.Adjustment ,Col3 = b.Col3 + b.Adjustment FROM ATable as A inner Join TableB as B ON a.ID = b.ID AND b.NeedsAdjustment = 1 ; UPDATE A ...


7

Updated Answer Response to @srutzky Another, albeit minor, issue with this "serializable transaction + OUTPUT clause" approach is that the OUTPUT clause (in its present usage) sends the data back as a result set. A result set requires more overhead (probably on both sides: in SQL Server to manage the internal cursor, and in the app layer to manage the ...


6

This is the separate DELETE operation I had in mind: DELETE m FROM dbo.Mapping AS m WHERE EXISTS (SELECT 1 FROM @Values WHERE LeftID = m.LeftID) AND NOT EXISTS (SELECT 1 FROM @Values WHERE LeftID = m.LeftID AND RightID = m.RightID); As I outline here, for a left anti-semi join, the NOT EXISTS pattern will often outperform the LEFT JOIN / NULL pattern....


5

No there isn't anything similar to what you are asking built into the product. Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway. It would be possible in theory for you to write some sort of script ...


5

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented). I can't think of any performance difference. The plans certainly look so similar that ...


5

Identity values cannot be updated. They can be inserted if IDENTITY_INSERT is turned on for that table. This code shows how that works: USE tempdb; CREATE TABLE dbo.TestIdentity ( ID INT NOT NULL IDENTITY(1,1) , SomeData VARCHAR(255) NOT NULL ); INSERT INTO dbo.TestIdentity (SomeData) VALUES ('This is a test'); --This works SET ...


5

UNTESTED as I do not have access to SQL Server at the moment, but try this, it should work, or at least point you in the right syntax direction for a Merge statement MERGE Employees emp USING UpdateToEmployees ute ON [emp].[empid] = [ute].[empid] WHEN MATCHED THEN UPDATE SET [emp].[empaddress] = [ute].[empaddress] ,[emp].[phone] = [ute].[phone] WHEN ...


5

Something needs to be in the WHEN MATCHED THEN clause, otherwise you don't get the Id back when the row exists. However, you don't need to update the target table. DECLARE @dummy int; ;WITH _ContactPhoneNumbers ( [PhoneNumber] ) AS ( SELECT DISTINCT ( [#ChannelData].[CallerAni] ) FROM ...


5

Here is at least a part of the problem: If you have two rows in HMI_Temp with the same value for sn but different values for cb (or any other column), which one should SQL Server pick? Here is a better pattern than MERGE, IMHO, which has all kinds of issues: INSERT dbo.HMI(stn,sn,cn,lp,cb) SELECT stn,sn,cn,lp,cb FROM ( SELECT stn,sn,cn,lp,cb,rn = ...


5

Using the sample data: DECLARE @T table ( Id integer IDENTITY NOT NULL PRIMARY KEY, [Hash] binary(16) NOT NULL INDEX h ); INSERT @T ([Hash]) VALUES (0x31F777F0804D301936411E3ECD760859); INSERT @T ([Hash]) VALUES (0xD64A593F3E9ACC972158D522A4289EA0); DECLARE @S table ( [Hash] binary(16) NOT NULL ); INSERT @S ([Hash]) VALUES (...


Only top voted, non community-wiki answers of a minimum length are eligible