SQL Server 2008 included some great new T-SQL enhancements that I’ve been looking into and thought I would share. The first is the combined variable declaration and initialization syntax: In previous versions of SQL Server, one would have to write the following in order to declare and initialize a variable:
declare @v_2005id int
set @v_2005id = 1
set @v_2005id = @v_2005id + 1
select @v_2005id as '@v_2005id'
Now in SQL 2008, this is streamlined:
declare @v_2008Id int = 1
set @v_2008Id +=1
select @v_2008Id as '@v_2008Id'
This is more abbreviated – always a good thing to save a few keystrokes.
Another enhancement is table constructors, which allows you to insert multiple rows within one SQL statement. As an example:
declare @v_temp table (ItemName varchar(25) not null, ItemPrice numeric(5,2) not null)
insert into @v_temp (ItemName, ItemPrice) values ('A new product', 38.27), ('Another Product', 29.95), ('Product 1', 57.28)
select * from @v_temp
Notice that I inserted 3 rows into the temp table by simply surrounding each row “group” with () instead of writing 3 distinct INSERT statements. The parenthesis are required between each insert “set” so that SQL Server can separate the individual insert lines. Once again, saving a few more keystrokes.
The next one is a bit more complicated – the combined Insert/Update/Delete statement, which is called MERGE in SQL Server. This one allows you do all three operations within one statement, instead of three. Let’s start by creating two tables and populating these two with data:
-- drop and (re)create the testing table
if exists (select 1 from sys.tables where [name] = 'tblPlayground')
begin
drop table tblPlayground
end
go
create table tblPlayground
(
Id int not null identity(1,1) primary key clustered
,ProductName varchar(25) not null
,ProductPrice numeric(5,2) not null default 0
,CreateDate date not null default SYSUTCDATETIME()
)
GO
insert into tblPlayground (ProductName, ProductPrice, CreateDate) values ('Product 1',27.95, '5/3/2009'), ('Product 2',33.47,'5/3/2009')
select * from tblPlayground
declare @v_temp table (ItemName varchar(25) not null, ItemPrice numeric(5,2) not null)
insert into @v_temp (ItemName, ItemPrice) values ('A new product', 38.27), ('Another Product', 29.95), ('Product 1', 57.28)
select * from @v_temp
In the above script, I created two tables and populated both of these with three rows each. There is one overlapping row between the two, two new rows in the second table, and one row in the first table that is not in the second table. What I want to do is merge the data in these two tables into the first table, with any missing rows in the second table being deleted from the first. With the new SQL Server MERGE command, I execute the following script:
Merge into tblPlayground as destination
using (select * from @v_temp) as secondary on (destination.ProductName = secondary.ItemName)
when matched then
update set destination.ProductPrice = secondary.ItemPrice
when not matched by target then
insert values (secondary.ItemName, secondary.ItemPrice, GETDATE())
when not matched by source then
delete; --<<-- ';' important!
-- after
select * from tblPlayground
go