三个 CTE 问题

发布于 2024-12-03 18:00:05 字数 481 浏览 1 评论 0原文

我希望做这样的事情：

WITH Hello (Name)
AS (
INSERT INTO MyTest
OUTPUT 
  inserted.Name
VALUES ('George')
)
SELECT * FROM Hello

简而言之，我希望输出只是成为 CTE 输入。有办法解决这个问题吗？

还有两个问题：

有没有办法构造一个空的CTE？
CTE 可以修改吗？我可以在构建后插入行吗？

更新我找到了主要问题的答案，目的是用任意数据填充 CTE：

;WITH stuff AS (
  SELECT * FROM ( VALUES
   (123, 1),
   (234, 1),
   (345, 0)
  ) [X](id, pref)
)
SELECT * FROM stuff

原文

I was hoping to do something like this:

WITH Hello (Name)
AS (
INSERT INTO MyTest
OUTPUT 
  inserted.Name
VALUES ('George')
)
SELECT * FROM Hello

In short, I was hoping the output would simply become the CTE input. Is there a way to fix this?

Two more questions:

Is there a way to construct an empty CTE?
Can a CTE be modified? Can I insert rows after it has been constructed?

UPDATE
I found the answer to my primary question, with the intent of filling a CTE with arbitrary data:

;WITH stuff AS (
  SELECT * FROM ( VALUES
   (123, 1),
   (234, 1),
   (345, 0)
  ) [X](id, pref)
)
SELECT * FROM stuff

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

琉璃繁缕 2024-12-10 18:00:05

另外两个问题

WITH Hello 
AS (
select  name
from sysobjects where 1 =0
)
SELECT * FROM Hello --no rows

-- ***  update  ***

create table testNow(id int)
insert testNow values(1)

;WITH Hello 
AS (select id from testNow)

update Hello set id = 2 -- update cte

SELECT * FROM testNow --table now has value 2

-- ***  insert   ***

;WITH Hello 
AS (
select id from testNow

)

insert Hello values( 3 )

SELECT * FROM testNow --table now has 2 rows

the two other questions

WITH Hello 
AS (
select  name
from sysobjects where 1 =0
)
SELECT * FROM Hello --no rows

-- ***  update  ***

create table testNow(id int)
insert testNow values(1)

;WITH Hello 
AS (select id from testNow)

update Hello set id = 2 -- update cte

SELECT * FROM testNow --table now has value 2

-- ***  insert   ***

;WITH Hello 
AS (
select id from testNow

)

insert Hello values( 3 )

SELECT * FROM testNow --table now has 2 rows

回复收藏 0 原文

~没有更多了~