Geeks With Blogs

News Dave's Mug View David Oliver's profile on LinkedIn Add to Technorati Favorites Blog Directory for Guildford, Surrey
Dave Oliver's Blog Enterprise Technology Thought Leadership in a FTSE 100

I know I don’t often talk about code, but I have recently had the good fortune to get back into playing around with the new version of SQL Server 2005. I think I’m going to do a few small posts on some of the new things I’ve found that I like.

Insert and Merge, finally your can ‘Upsert’!

Upsert is a mythical feature where you can insert and update data with one command. It's not quite in one command but the effect is the same with a simple set of syntax.

MERGE INTO DavesTable

            USING DavesTempTable

                        ON DavesTable.MatchingField1 = DavesTempTable.MatchingField1

WHEN MATCHED THEN

            UPDATED UpdatedField1 = DavesTempTable.UpdatedField1

WHEN NOT MATCH THEN

            INSERT VALUES(DavesTempTable.MatchingField1, DavesTempTable.UpdateField1)

Although it’s nice to finally have this feature and the syntax is simple and elegant I’m wondering if many people will actual use it in anger.

UPDATE: August 2008: Well the MERGE command didn't finally make it until SQL Server 2008. It didn't make 2005 and was pulled in the RTM version.

Now the TOP command has always been a great feature but what has always been a right pain is that it has to be a constant in SQL Server 2000 which has really limited it’s use.

Now this syntax is valid!

-- show the first x products names in alphabetical order.

DECLARE @x bigint

SET @x = 25

SELECT TOP (@x) ProductID, Name

FROM Product

ORDER BY NAME

Just like the previous version of the TOP command it was well used in scalar statements. With the ability to pass the value to the statement from the outside I can see this going to be a very powerful new feature used for reporting especially as the PERCENT suffix is still valid. The only departure from the previous version is that you will need to use brackets if the value is a variable, use a constant and you won’t need them.

Posted on Saturday, August 20, 2005 7:45 PM | Back to top


Comments on this post: New and Changed SQL Server 2005 commands: Part1

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
It doesnt work , Why ? CTP 08/2005 ?
Left by Yossi on Sep 30, 2005 8:01 AM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Looking back at the article ... it should MERGE INTO not just MERGE. Sorry for the translation error from editor to article.
Left by Dave Oliver aka Channel 9's Sabo on Sep 30, 2005 5:39 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Hi Dave,
I tried again and the TSQL got an error like
[Incorrect syntax near the keyword INTO]
Look at the syntax :

MERGE INTO ATable
USING BTable
ON ATable.AKey = BTable.BKey
WHEN MATCHED THEN
UPDATED AValue = BTable.BValue
WHEN NOT MATCH THEN
INSERT VALUES(BTable.BKey, BTable.BValue)

10x again

Bye
Left by Yossi on Oct 03, 2005 8:07 AM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Let me get my grubby mits on the latest SQL Server 2005 version and I'll figure out what I'm doing wrong!

I'm such a muppet, never let me code again! :-(
Left by Dave Oliver aka Channel 9's Sabo on Oct 03, 2005 5:28 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Hi Dave, 10x , You are fine and your BLOG too ! Bye
Left by Yossi on Oct 05, 2005 5:01 PM

# need a solution
Requesting Gravatar...
"mergo into"syntax not working in sql server 2005.can i get a solution for this
Left by karthik on Jul 13, 2006 10:31 AM

# merge syntax giving error
Requesting Gravatar...
I types the code

merge into mis_list a
using (select foracid from gam where schm_code ='LNPHG') g
on (a.foracid = g.foracid)
when matched then
update set a.foracid = g.foracid
when not matched then
insert values (g.foracid)

Its giving error as

on (a.foracid = g.foracid)
*
ERROR at line 3:
ORA-00904: "A"."FORACID": invalid identifier

Can u help me out????
Left by meera on Sep 13, 2006 9:31 AM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.MatchingField , MyTable.MatchingField
WHEN MATCHED THEN
UPDATE UpdateField = MyTempTable.UpdateField
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField,MyTempTable.UpdateField)


MERGE specifies the concerned tables for the data upsert.
USING specifies the data origin.
ON determines the used fields for the correspondence.
If the fields are equal, the section code WHEN MATCHED THEN will be executed
In the other case,the section code WHEN NOT MATCHED THEN will be executed.
However, it's heavier to recover the values in a temporary table in order to make a comparison in the target table. This solution asks to write lot of code and can decrease consequently the performances. This is due to the time need to recover the data in the temporary table.

Consequently,for few records, the MERGE instruction is very practical.
Left by Dave Oliver on Sep 16, 2006 8:55 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
create table MyTable1 (myId int, myVal varchar(20))

insert MyTable1 values(1,'one')
insert MyTable1 values(2,'two')
insert MyTable1 values(3,'three')
insert MyTable1 values(4,'four')
insert MyTable1 values(5,'five')
insert MyTable1 values(6,'66')

create table MyTable2 (myId int, myVal varchar(20))
insert MyTable2 values(1,'11')
insert MyTable2 values(2,'22')
insert MyTable2 values(3,'33')
insert MyTable2 values(4,'44')
insert MyTable2 values(5,'55')


MERGE INTO dbo.MyTable2
USING MyTable1
ON MyTable1.myId = MyTable2.myId
WHEN MATCHED THEN
UPDATE myVal = MyTable1.myVal
WHEN NOT MATCHED THEN
INSERT VALUES(MyTable1.myId, MyTable1.myVal)

-- Get Error like Yossi
Left by aspeng on Oct 16, 2006 3:16 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
MERGE INTO EMPLOYEE_NEW
USING EMPLOYEE
ON EMPLOYEE_NEW.EMPLOYEE_ID=EMPLOYEE.EMPLOYEE_ID
WHEN MATCHED THEN
UPDATE SET EMPLOYEE_NEW.EMP_NAME=EMPLOYEE.EMP_NAME
WHEN NOT MATCHED THEN
INSERT VALUES (EMPLOYEE.EMPLOYEE_ID,EMPLOYEE.EMP_NAME)

i executed above code but it is not working. It is giving error like
@Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INTO'.

Can you tell me the solution for this.


Left by Praveen on Nov 29, 2006 5:16 AM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
EXCEPT - INTERSECT
Left by chris on Feb 05, 2007 8:35 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
I challenge anyone to submit a Microsoft URL saying anything about the alleged SQL Server MERGE statement. Oracle has had a Merge statement since 9i (or maybe even 8i, it's been a long time). SQL Server has just added the ability to simulate this functionality with EXCEPT and INTERSECT, ie

-- UPDATE Destination FROM (Source INTERSECT Destination) SET....

-- INSERT INTO Destination FROM (Source EXCEPT Destination) VALUES...

Left by Louis on Apr 06, 2007 8:17 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
re "Oracle has had a Merge statement since 9i"

What's your point?
Left by Clonus on May 28, 2007 1:22 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
MERGER - Performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.
This feature is with SQL Serve 2008.
Left by Arshad on Apr 26, 2008 7:53 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
MERGE SQL Statement - Performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.
This feature is with SQL Serve 2008.

*Please Ignore previous comment.
Left by Arshad Ali on Apr 26, 2008 7:55 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Louis - Can you please explain your statements. It does not even make sense.

-- UPDATE Destination FROM (Source INTERSECT Destination) SET....

-- INSERT INTO Destination FROM (Source EXCEPT Destination) VALUES...


Show me how this works in real life.

My destination table called DCT has 3 columns namely DCTID (int), FirstName (varchar50), lastName (varchar50). It has 10 rows in it.

This is my INTERSECT statement:
SELECT 2, 'John', 'Smith' INTERSECT SELECT * from DCT;

This is my EXCEPT statement:
SELECT 2, 'John', 'Smith' EXCEPT SELECT * from DCT;

I know I can write that a lot more eloquently, but I want to make sure you understand what I am trying to do.

Thank you in advance.
Left by BaasGaas on Jun 13, 2008 10:01 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
Merge into was in SQL Server 2005 Beta 2 however was pulled from RTM. It is scheduled to be included in SQL Server 2008.
Left by MDH on Jun 18, 2008 10:54 PM

# re: New and Changed SQL Server 2005 commands: Part1
Requesting Gravatar...
I'm not sure that it will appear in 2008 either, if the truth be told. Funny thing is, I would rather see this working than many of the over new features.
Left by Dave Oliver on Jun 18, 2008 11:00 PM

Comments have been closed on this topic.
Copyright © Dave Oliver | Powered by: GeeksWithBlogs.net | Join free