sql server 2008 新特性

最新推荐文章于 2024-02-01 21:45:10 发布

Houluoxuan_168

最新推荐文章于 2024-02-01 21:45:10 发布

阅读量596

点赞数 1

分类专栏： SQL Server 文章标签： sql server insert integer null table c

本文链接：https://blog.csdn.net/Houluoxuan_168/article/details/6840900

版权

SQL Server 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1.同时插入多行，

insert into a values(2,'C'),(3,'D'),(4,'E')；

or 可套查询，如：

insert into a values((select top 1 id from b),(select top 1 name from c))

定义表变量，定义完以后在整个DB中有效，且是持久的，如此可以在存储过程，函数中作为参数传递。

IF (SELECT COUNT(*) FROM sys.table_types
WHERE name = 'TVPParam' AND schema_id = 1) = 0
  CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)
GO

以后可以不再使用union all 来组合查询了。

SELECT * FROM (VALUES('John Smith', 25, 5),('Jane Dow', 36, 6))
mytable(name, age, score)
GO

4.合并两表结构相同的表，不同也可以，但是意义不大。

MERGE Customers C
  USING QuarterlyUpdate Q
  ON C.customer_id =Q.customer_id
  WHEN MATCHED THEN 
   UPDATE SET 
    C.customer_name = Q.customer_name,
    C.policy_type = Q.policy_type,
    C.deductable = Q.deductable,
    C.number_of_claims += new_claims
  WHEN NOT MATCHED BY SOURCE THEN
    DELETE
  WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES(customer_name, policy_type, deductable, new_claims)
    OUTPUT $action,
      INSERTED.customer_id,
      INSERTED.customer_name AS [New Name],
      INSERTED.policy_type AS [New Policy],
      INSERTED.deductable AS [New Deductable],
      INSERTED.number_of_claims AS [New Number Of Claims],
      DELETED.customer_id AS [Original Customer Id],
      DELETED.customer_name AS [Original Name],
      DELETED.policy_type AS [Original Policy],
      DELETED.deductable AS [Original Deductable],
      DELETED.number_of_claims AS [Original Number Of Claims];
GO

按多组一次执行分组。

CREATE TABLE T (
 a int,
 b int,
 c int,
 x int
)
GO

INSERT T VALUES(1,2,3,8)
INSERT T VALUES(1,2,4,1)
INSERT T VALUES(1,2,5,5)
INSERT T VALUES(1,3,4,9)
GO

SELECT a, b, c, SUM(x) AS sumx FROM T
GROUP BY GROUPING SETS (a, (b), (a, b, c))
GO

a   b   c   sumx
1   2   3   8
1   2   4   1
1   2   5   5
N   2   N   14
1   3   4   9
N   3   N   9
1   N   N   23

其效果与下面传统的写法相同，但是效率会提高。

SELECT a, NULL AS b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY a
UNION ALL
SELECT NULL AS a, b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY b
UNION ALL
SELECT a, b, c, SUM(x) AS sumx FROM T
GROUP BY a, b, c
GO

下面查询列出所有的组合，cube 解释为立方体。

SELECT a, b, c, SUM(x)
FROM T
GROUP BY a, b, c
WITH CUBE