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:
- 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
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
精彩评论