开发者

Three CTE Questions

开发者 https://www.devze.com 2023-04-03 18:01 出处:网络
I was hoping to do something like this: WITH Hello (Name) AS ( INSERT INTO My开发者_开发问答Test OUTPUT

I was hoping to do something like this:

WITH Hello (Name)
AS (
INSERT INTO My开发者_开发问答Test
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:

  1. Is there a way to construct an empty CTE?
  2. 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


the two other questions

1)

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

2)

-- ***  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

精彩评论

暂无评论...
验证码 换一张
取 消