SQLServer 表值构造函数(Transact-SQL)的使用

目录

• 1、本文内容
• 2、语法
• 3、自变量
• 4、限制和局限
• 5、数据类型
• 6、示例
• 6.1、插入多行数据
• 6.2、使用 DEFAULT 和 NULL 值插入多行
• 6.3、在 FROM 子句中将多个值指定为派生表
• 6.4、在 MERGE 语句中将多个值指定为派生源表
• 6.5、 插入超过 1000 行
• 7、另请参阅

VALUES ( <row value expression list> ) [ ,...n ]   
  
<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  
  
<row value expression> ::=  
    { DEFAULT | NULL | expression }

CREATE TABLE dbo.t_MyProducts (NameInfo NVARCHAR(50), Price decimal(null,2));  
GO  
-- This statement fails because the third values list contains multiple columns in the subquery.  
INSERT INTO dbo.t_MyProducts (NameInfo, Price)  
VALUES (N'鼠标', 25.50),  
       (N'键盘', 130.00) ,
       (SELECT Title, Price FROM T_Product WHERE ID = 2);  
GO

INSERT INTO dbo.t_MyProducts (Name, ListPrice)  
VALUES (N'鼠标', 25.50),  
       (N'键盘', 130.00) ,
       ((SELECT Title FROM T_Product WHERE ID = 2),  
        (SELECT Price FROM T_Product WHERE ID = 2));  
GO

select * from dbo.t_MyProducts
GO

CREATE TABLE dbo.t_datatype (col_a INT, col_b CHAR);  
GO  
INSERT INTO dbo.t_datatype  VALUES (null,'a'), (null, 1);  
GO

INSERT INTO dbo.t_datatype VALUES (null,'a'), (null, CONVERT(CHAR,1));

INSERT INTO t_MyProducts
VALUES (N'华为手机',6888.88), (N'荣耀手机',6688.99),
       (N'小米手机',5999.88);  
GO

SELECT * FROM dbo.t_MyProducts

CREATE TABLE T_MySalesReason
(  
 ID int IDENTITY(null,1) NOT NULL,  
 NameInfo NVARCHAR(32) NULL ,  
 ReasonType NVARCHAR(32) NOT NULL DEFAULT N'新能源汽车' 
);  
GO  
INSERT INTO T_MySalesReason   
VALUES (N'问界M9',N'增程'), (N'比亚迪唐', DEFAULT), (NULL, N'燃油车');  
  
SELECT * FROM T_MySalesReason;  

SELECT col_a,col_b FROM (VALUES (null, 2), (null, 4), (null, 6), (null, 8), (null, 10) ) AS MyTable(col_a, col_b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT NameInfo,a.ReasonType,b.Name
FROM T_MySalesReason AS a  
  INNER JOIN (VALUES (N'问界M9'), (N'比亚迪唐'), (N'宝马X3')) AS b (Name)   ON a.NameInfo = b.Name;
-- Used in an cross join to specify values to return.  
SELECT NameInfo,a.ReasonType,b.Name
FROM T_MySalesReason AS a  
  CROSS JOIN (VALUES (N'问界M9'), (N'比亚迪唐'), (N'宝马X3')) AS b (Name);

-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO T_MySalesReason AS Target  
USING (VALUES (N'问界M9',N'绿牌'), (N'比亚迪唐', N'绿牌'), (N'宝马X3', N'蓝牌'))  
       AS Source (NewName, NewReasonType)  
ON Target.NameInfo = Source.NewName  
WHEN MATCHED THEN  
  UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
  INSERT (NameInfo, ReasonType) VALUES (NewName, NewReasonType)  
OUTPUT $action INTO @SummaryOfChanges;  
  
-- Query the results of the table variable.  
SELECT Change, COUNT(*) AS CountPerChange  
FROM @SummaryOfChanges  
GROUP BY Change;

CREATE TABLE dbo.T_TestValue ([Value] INT);  
  
INSERT INTO dbo.T_TestValue ([Value])  
SELECT [NewVal]
FROM   (VALUES (0), (1), (2), (3), ..., (5000)) AS Temp01 ([NewVal]);