SQL Server 2008 New T-SQL shorthand syntax

1. You can assign values directly when defining variables

DECLARE @Id int = 5

2.Insert statements can insert multiple rows of data at once

INSERT into StateList VALUES (@Id, ' WA '), (@Id + 1, ' FL '), (@Id + 2, ' NY ')

3. Support + = operator

SET Stateid + = 1

The output function in SQL Server 2005 can return the contents of both insert,update and delete, and the output in 2008 also has this function. Here's how to use the new T-SQL statement merge combination of output with 2008:

Create the following table:

[C-sharp]View Plaincopy

2. CREATE TABLE Book (
4. ISBN varchar (PRIMARY KEY),
6. Price Decimal,
8. Shelf int)
12. CREATE TABLE Weeklychange (
14. ISBN varchar (PRIMARY KEY),
16. Price Decimal,
18. Shelf int)
22. CREATE TABLE Bookhistory (
24. Action nvarchar (10),
26. NEWISBN varchar (20),
28. Newprice Decimal,
30. newshelf int,
32. OLDISBN varchar (20),
34. Oldprice Decimal,
36. oldshelf int,
38. Archivedat datetime2)

The SQL statement is

[C-sharp]View Plaincopy

1. MERGE Book as B
2. USING Weeklychange as WC
3. On B.ISBN = WC. Isbn
4. When matched and (B.price <> WC. Price OR b.shelf <> WC. Shelf) Then
5. UPDATE SET b.price = WC. Price, b.shelf = WC. Shelf
6. When isn't matched then
7. INSERT VALUES (WC. ISBN, WC. Price, WC. Shelf)
8. OUTPUT $action, inserted.*, deleted.*, Sysdatetime ()
9. into Bookhistory;

The result set is:

SELECT * from Bookhistory
GO

Action newisbn newprice newshelf oldisbn oldprice oldshelf archivedat
------ ------- -------- -------- ------- -------- -------- ---------------------------
UPDATE a 101 1 a 100 1 2007-11-25 14:47:23.9907552
INSERT C 3 null NULL NULL 2007-11-25 14:47:23.9907552

There are two output cases of INSERT and update. If you only need one of these, you can filter it in the following way:

[C-sharp]View Plaincopy

2. INSERT into book (ISBN, Price, Shelf, Archivedat)
4. SELECT ISBN, Price, Shelf, GETDATE () from
6. (MERGE book as B
8. USING Weeklychange as WC
10. On B.ISBN = WC. ISBN and B.archivedat is NULL
12. When matched and (B.price <> WC. Price OR b.shelf <> WC. Shelf) Then
14. UPDATE SET price = WC. Price, Shelf = WC. Shelf
16. When isn't matched then
18. INSERT VALUES (WC. ISBN, WC. Price, WC. Shelf, NULL)
20. OUTPUT $action, WC. ISBN, Deleted.price, deleted.shelf
22. ) Changes (Action, ISBN, Price, Shelf)
24. WHERE Action = ' UPDATE ';

SQL Server 2008 New T-SQL shorthand syntax