Using Common Table Expression (CTE) for multiple insert and update a t

[fletchsod]

How do I use Common Table Expression (CTE) that would not only insert data into a table but update a row if the data exist?

Sort of like MERGE feature or the IF MATCH THEN UPDATE ELSE INSERT feature.

I tried with the MERGE but it doesn't seem to be supported.

I"m using Sql-2008.

Thanks...

 

[hmckillop]

In SQL Server 2005 you can use OUTPUT clause of an UPDATE statement here is a sample:

create table permanent(id int, d float, comment varchar(15))
go
insert into permanent values(1, 10., 'Original Row')
insert into permanent values(2, 10., 'Original Row')
insert into permanent values(3, 10., 'Original Row')
go

And you want to modify it with incoming values from a staging table

create table staging(id int, d float)
go
insert into staging values(2, 15.)
insert into staging values(3, 15.)
insert into staging values(4, 15.)
go

then

declare @updated_ids table(id int)
update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id

insert into permanent
select id, d, 'New Row' from staging where id not in(select id from @updated_ids)

the output is below

select * from permanent
go

id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 15 Modified Row
3 15 Modified Row
4 15 New Row

I thought sql2008 had merge though

http://www.builderau.com.au/program...-MERGE-statement/0,339028455,339283059,00.htm

"I'm living so far beyond my income that we may almost be said to be living apart

 

[fletchsod]

I have a "Common Table Expression". Recently found a link at "

http://www.sqlmag.com/Article/ArticleID/97965/sql_server_97965.html

" that said I can use the WITH option. So, a further step in the right direction but still can't get it to work as I'm trying to tie-in the data from the WITH option to the USING option.

 

[fletchsod]

Finally, it works now... The insert works but not hte update, so once I get that fixed than I'm done... Whew!