Introduction:
In this article,i am going to explain how to use the new t-sql shorthand syntax in sql server 2008.
Main:
If you write a lot of T-SQL code, you will definitely appreciate these minor (but extremely handy) enhancements to the language syntax. These features may pale in the face of everything else new in SQL Server 2008, but they are welcome timesavers just the same and further demonstrate Microsoft’s continued investment and innovation in T-SQL.
We can finally declare and initialize variables with a single statement, and compound assignment operators are supported as well. Plus, row constructors allow you to build multiple rows of data inline. For example, you can now insert multiple rows of data with a single INSERT statement that wraps the operation in an implicit transaction.
CREATE TABLE StateList(StateId int, StateName char(2))
GO
-- Declare variable and assign a value in a single statement
DECLARE @Id int = 5
-- Insert multiple rows in a single statement with IDs 5, 6, and 7
INSERT INTO StateList VALUES(@Id, 'WA'), (@Id + 1, 'FL'), (@Id + 2, 'NY')
-- Use compound assignment operator to increment ID values to 6, 7, and 8
UPDATE StateList
SET StateId += 1
-- View the results
SELECT * FROM StateList
CREATE TABLE StateList(StateId int, StateName char(2)) GO -- Declare variable and assign a value in a single statement DECLARE @Id int = 5 -- Insert multiple rows in a single statement with IDs 5, 6, and 7 INSERT INTO StateList VALUES(@Id, 'WA'), (@Id + 1, 'FL'), (@Id + 2, 'NY') -- Use compound assignment operator to increment ID values to 6, 7, and 8 UPDATE StateList SET StateId += 1 -- View the results SELECT * FROM StateList |
And here is the output:
StateId StateName
——- ———
6 WA
7 FL
8 NY
(3 row(s) affected)
Conclusion:
Hope this helps,
Happy Coding.