"[solved]"Insert data from CTE into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

"[solved]"Insert data from CTE into a table

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-04 : 07:43:34

How can I insert data from the below query into a target_table(which as 4 columns of similar type)?


with t1 as (
            select c1,c2,c3,cast('<a><b>' + replace(c4,',','</b><b>') + '</b></a>' as xml) c4 from sample_table
           )
select  c1,c2,c3,t2.c4.value('.','varchar(max)') 
  from  t1 cross apply c4.nodes('/a/b') as t2(c4) 

--below is the result of the above query
c1    c2     c3     c4
-------------------------------------------------
sys	!	0	root
stf	!	1	sec,eva,actr1,act2,act3,act4,act5,act6,actr7,act8,act9
xin	!	2	root,bin

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-04 : 08:14:35

the below is working for me

with t1(FirstCol,SecCol,ThirdCol,FourthCol) as (
select c1,c2,c3, cast('<a><b>' + replace(c4,',','</b><b>') + '</b></a>' as xml)c4 from sample_table
)

insert into sample_target
select FirstCol,SecCol,ThirdCol,c4.value('.','varchar(max)')
from t1 cross apply FourthCol.nodes('/a/b') as t2(c4)

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-04 : 10:33:13

How can mark this as answered??

-Neil

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-04 : 10:42:51

quote:
Originally posted by aakcse

How can mark this as answered??

-Neil

Go to your first post in this thread and click "edit topic". Then you are able to change the topic name for example to:
"[solved] Insert data from CTE into a table"

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 17:29:13

instead of the cte and xml logic you can simply do this


select c1,c2,c3,f.Val
from t1 
cross apply dbo.ParseValues(t1.c4,',')f

ParseValues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources