|
[ ABOUT THE AUTHOR ]
Introduction
As the release of SQL Server 2008 is just around the corner, we all are looking for the new features of this new SQL Server version. Among the many new features and functionalities available with SQL Server 2008, this article series looks into T-SQL enhancements done for the new version.
This article is based on the February CTP or CTP 6 of the SQL Server 2008 and there might be some changes when Microsoft releases final RTM. (Microsoft has announced that CTP 6 is a feature completed version and will not add new features to it)
MERGE
In case of inserts and updates records to a tables, you have to check whether you have to perform an insert or update. If the records already exists, you need to do an update and no record exists you need to do an insert.
IF FOUND
THEN UPDATE
ELSE
INSERT;
As you can see from the above pseudo code, you have to write several statements to achieve this. However, in SQL Server 2008 you have new T-SQL command called MERGE to do the both types of operations. Let us see how we can use MERGE command with a simple example.
Let us create two tables namely tblSource and tblTarget.
CREATE TABLE tblSource
(ID INT IDENTITY(1,1),
Name VARCHAR(50))
GO
CREATE TABLE tblTarget
(ID INT IDENTITY(1,1),
Name VARCHAR(50))
Next we need to insert some sample data into two tables.
INSERT INTO tblSource (Name)
VALUES ('Dinesh Asanka'),('Peter Such'),('Roland Howard')
INSERT INTO tblTarget (Name)
VALUES ('Dinesh Asanka'),('Pete Such')
You may have noticed that there is another new enhancement in the above INSERT. In the previous version of SQL Server, you may have to write three insert statements to insert three records to same table. However, in case of SQL Server 2008, you can write a single insert by separating row data by comma.
After running the above scripts, following are the outputs you will get.
As you can observe, in the target table ID 2 record is different from the tblSource table while ID 3 is not available in the target table. So by using MERGE command ID 2, should be updated and ID 3 should be inserted.
MERGE tblTarget
USING
(
SELECT
id,
Name
FROM tblSource
) Source ON tblTarget.id = Source.id
WHEN MATCHED THEN UPDATE SET tblTarget.Name = Source.Name
WHEN TARGET NOT MATCHED THEN INSERT VALUES (Name);
Above is the MERGE command. From the USING part it gives you the columns for the source table. And WHEN MATCHED UPDATE gives you the update statement while TARGET NOT MATCHED gives you, what you should when match is not found.
So, after running the above script, your target table should like following.
And you can see that now the tblTarget is same as tblSource.
Apart from insert and update MERGE has the ability to delete the data when no matching. (Many database systems call MERGE command as UPSERT. Reason for SQL Server 2005 to call this function MERGE might be the reason that it has the delete function along with Insert and Update)
MERGE tblTarget
USING
(
SELECT
id,
Name
FROM tblSource
) Source ON tblTarget.id = Source.id
WHEN MATCHED THEN UPDATE SET tblTarget.Name = Source.Name
WHEN TARGET NOT MATCHED THEN INSERT VALUES (Name)
WHEN SOURCE NOT MATCHED THEN DELETE;
In the above command, you can see there is a new additional line WHEN SOURCE NOT MATCHED THEN DELETE. What this does means is that, if you can’t find any matching records n the source table , relevant record will be removed or deleted from the target table.
Followings are the output of source table and target tables before and after the MERGE command.
MERGE command can be used in SQL Server integration Services as an alternative to type1 SCD. Although a MERGE statement typically requires a staging table in this scenario, the performance of the MERGE statement usually exceeds that of the row-by-row lookup performed by the Lookup transformation. MERGE is also useful when the large size of a lookup table would test the memory that is available to the Lookup transformation for caching its reference table.
Table Value Parameters
If you are using stored procedures in SQL Server 2005, I am sure you love to have the opportunity of passing data as table format into a stored procedure as a parameter, which was not possible with SQL Server 2005. Many developers are using XML functions to pass table type data into stored procedures with SQL Server 2005.
Let us see how this feature works with a sample. Let us say we want to insert multiple records to a table.
Following script will create a table name called tblNames which we are going to insert records from stored procedure.
CREATE TABLE tblNames
(ID INT IDENTITY (1,1),
NAME VARCHAR(50))
Next, we will create a Type which is new in SQL Server 2008.
CREATE TYPE Names AS TABLE
(
[Name] [varchar](50) NULL
)
GO
Next is to write a stored procedure to insert data to tblNames table. You can see that, input parameter is Names which has a Names types and it is a Table type. In the Stored procedure body, input parameter is used to insert data.
CREATE PROCEDURE usp_InsertEmployees
(
@TableVariable Names READONLY
)
AS
BEGIN
INSERT INTO tblNames
(
Name
)
SELECT
Name
FROM
@TableVariable
END
GO
Following is how you use the above stored procedure.
DECLARE @DataVariable As Names
INSERT INTO @DataVariable (Name) VALUES ('Dinesh'),('Asanka')
EXECUTE usp_ InsertEmployees
@TableVariable = @DataVariable
Select * from tblNames will give you the following results.
However, there are two issues with table value parameter feature which will make users to think twice before using it. Main obstacle is that, you cannot have table as output parameters. So still you have to use XML functions like OPENXML in case you need to output a table from a stored procedure. Apart from this, table value parameter is a read only parameter so that you cannot do updates to the table within the stored procedure. In case you need to update the table, you need to copy input read-only table to another new table and update new table. However, in case of a large table there will be performance issues.
Row Constructors
Though we have discussed about row constructors in previous examples, for the sake of completeness we will discuss this feature separately.
In the previous versions of SQL Server to insert multiple records, you had to write one insert command for each record. However, with SQL Server 2008, there is an option where you can insert multiple numbers of records in one statement.
Following script is to insert data into a table using old methods.
INSERT INTO tblSampleTable (IDCol, CharVal)
VALUES (1, 'One')
INSERT INTO tblSampleTable (IDCol, CharVal)
VALUES (2, 'Two')
INSERT INTO tblSampleTable (IDCol, CharVal)
VALUES (3, 'Three')
INSERT INTO tblSampleTable (IDCol, CharVal)
VALUES (4, 'Four')
This is the new way of doing it in SQL Server 2008 by using row constructors.
INSERT INTO tblSampleTable (IDCol, CharVal)
VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four')
Conclusion
We have discussed three features of new T-SQL functionalities in SQL Server 2008 namely Merge, Table Value Parameters and Row constructors. We will discuss further two T-SQL functions in the next article.
References
|