创建表变量的语法和创建表的相似,不同的是使用DECLARE关键字,并且表名有@符号前缀:
DECLARE @TableName TABLE
(column_name <data_type>[NULL l NOT NULL][,…n])
DECLARE @ProductCostStatistics TABLE
(
ProductID int NOT NULL PRIMARY KEY,
AvgStandarCost money NOT NULL,
ProductCount int NULL
)
INSERT @ProductCostStatistics
(
ProductID,
AvgStandarCost,
ProductCount
)
SELECT ProductID,
AVG(StandardCost) AvgStandardCost,
COUNT(ProductID) Rowcnt
FROM Production.ProductCostHistory
GROUP BY ProductID
SELECT TOP 3 *
FROM @ProductCostStatistics
ORDER BY ProductCount