SQL Server Magazine

SQL Server 2008’s new statements extend the T-SQL feature set. Let’s take a guided tour of some of the most important statements. Later, in upcoming columns, I’ll point out more T-SQL enhancements you’ll find useful.

1. DECLARE Statement
With DECLARE, SQL Server 2008 offers the ability to assign values to a variable in its declaration. You can assign values to most data types, including SQLCLR data types, but not to TEXT, NTEXT, or IMAGE data types. Here’s an example:

 DECLARE @MyName varchar(20)=’Michael’

2. Compound Assignment Operators
SQL Server 2008 supports compound assignments and provides these compound operators: +=, -=, /=, %=, &=, |=, and ^=. The following statement uses the new compound addition operator to add two plus two:

 DECLARE @MyNumber int = 2
  SET @MyNumber += @myNumber

3. Row Constructor
Sometimes called Table Value Constructors, SQL Server 2008’s new Row Constructor lets you insert multiple rows by using a single statement. In the following example, I used a single INSERT statement to insert three rows in a table named MyTable:

CREATE TABLE MyTable
  (ID int, Item varchar(20))
INSERT INTO MyTable VALUES
  (1, ‘Bike’), (2, ‘Car’), (3, ‘Truck’)

4. MERGE Statement
You can selectively merge the contents of two tables. To merge the previous example, MyTable, with the contents of a new table, MyTable2, which contains duplicate data as well as new data, I used the MERGE statement

 CREATE TABLE MyTable2
  (ID int, Item varchar(20))
  INSERT INTO MyTable2 VALUES
  (1, ‘Bike’), (2, ‘Van’), (4, ‘Motorcycle’)
  GO
  MERGE MyTable2 AS TargetTable
  USING (SELECT ID, Item FROM MyTable)
  SourceTable
  ON (TargetTable.ID = SourceTable.ID)
  WHEN TARGET NOT MATCHED
  THEN INSERT VALUES (ID, Item)
  WHEN SOURCE NOT MATCHED
  THEN UPDATE SET Item = Item;
  GO

where MyTable2 is the merge target (i.e., the table to be updated with merged data) and MyTable is the merge source. If the value in the ID column is matched, the Item value is updated. Otherwise a new row is inserted in MyTable2.

“SQL Server 2008: Enter the Data Collector,” InstantDoc ID 99420

End of Article