求一个sql server 2008 的存储过程

[xa178188]

现在有表A， 里面字段 mobile为 (text类型)， content (varchar(1000))。数据行数不定
mobile content
150137447xx 验证码
154866256xx 456
154841255xx,189554742xx,155668484xx 789
第三行数据mobile用英文逗号分隔。
现在需求一个循环把这几条数据插入表B
最终的表B结果
mobile content
150137447xx 验证码
154866256xx 456
154841255xx 789
189554742xx 789
155668484xx 789

求大神，在线等。

[卖水果的net]

create table test(mobile varchar(50),  content varchar(10))
go
insert into test values
('150137447xx', '验证码'),
('154866256xx','456'),
('154841255xx,189554742xx,155668484xx','789')
go
with m as (
select mobile + ',' mobile, content from test 
)
select reverse(substring(reverse(substring(m.mobile,1,s.number-1)), 
						0 ,
						charindex(',',reverse(substring(m.mobile,1,s.number-1)) +',')
						)
		) as item ,
m.content
 from m, master..spt_values s
where len(m.mobile) >=s.number and s.type ='p'
 and substring(m.mobile,s.number,1)  =','
go
drop table test 
go


(3 行受影响)
item                                                content
--------------------------------------------------- ----------
150137447xx                                         验证码
154866256xx                                         456
154841255xx                                         789
189554742xx                                         789
155668484xx                                         789

(5 行受影响)

[xa178188]

@卖水果的net 大神， 我需要一个完整的存储过程， 不是太懂这个。 麻烦了。

[xa178188]

@卖水果的net 并且数据是要插入B表的。

[xa178188]

引用 1 楼 wmxcn2000 的回复:

create table test(mobile varchar(50),  content varchar(10))
go
insert into test values
('150137447xx', '验证码'),
('154866256xx','456'),
('154841255xx,189554742xx,155668484xx','789')
go
with m as (
select mobile + ',' mobile, content from test 
)
select reverse(substring(reverse(substring(m.mobile,1,s.number-1)), 
						0 ,
						charindex(',',reverse(substring(m.mobile,1,s.number-1)) +',')
						)
		) as item ,
m.content
 from m, master..spt_values s
where len(m.mobile) >=s.number and s.type ='p'
 and substring(m.mobile,s.number,1)  =','
go
drop table test 
go


(3 行受影响)
item                                                content
--------------------------------------------------- ----------
150137447xx                                         验证码
154866256xx                                         456
154841255xx                                         789
189554742xx                                         789
155668484xx                                         789

(5 行受影响)

大神， 我需要一个完整的存储过程，并且数据是要插入B表的。 不是太懂这个。 麻烦了。

[卖水果的net]

-- 知音啊，，你懒的程度，都快赶上我了。。 

create table test(mobile varchar(50),  content varchar(10))
go
insert into test values
('150137447xx', '验证码'),
('154866256xx','456'),
('154841255xx,189554742xx,155668484xx','789')
go
create table b( mobile varchar(50), content varchar(10))
go
-- 创建这个过程
create proc sp_xxx 
as 
begin
	with m as (
	select mobile + ',' mobile, content from test 
	)
	insert into b 
	select reverse(substring(reverse(substring(m.mobile,1,s.number-1)), 
							0 ,
							charindex(',',reverse(substring(m.mobile,1,s.number-1)) +',')
							)
			) as item ,
	m.content
	 from m, master..spt_values s
	where len(m.mobile) >=s.number and s.type ='p'
	 and substring(m.mobile,s.number,1)  =','
end  
go
-- 执行这个过程 
exec sp_xxx 
go
-- 查询表 b 的数据
select * from b 
go
drop proc sp_xxx
go
drop table test , b 
go


(3 行受影响)

(5 行受影响)
mobile                                             content
-------------------------------------------------- ----------
150137447xx                                        验证码
154866256xx                                        456
154841255xx                                        789
189554742xx                                        789
155668484xx                                        789

(5 行受影响)

[xa178188]

引用 5 楼 wmxcn2000 的回复:

-- 知音啊，，你懒的程度，都快赶上我了。。 

create table test(mobile varchar(50),  content varchar(10))
go
insert into test values
('150137447xx', '验证码'),
('154866256xx','456'),
('154841255xx,189554742xx,155668484xx','789')
go
create table b( mobile varchar(50), content varchar(10))
go
-- 创建这个过程
create proc sp_xxx 
as 
begin
	with m as (
	select mobile + ',' mobile, content from test 
	)
	insert into b 
	select reverse(substring(reverse(substring(m.mobile,1,s.number-1)), 
							0 ,
							charindex(',',reverse(substring(m.mobile,1,s.number-1)) +',')
							)
			) as item ,
	m.content
	 from m, master..spt_values s
	where len(m.mobile) >=s.number and s.type ='p'
	 and substring(m.mobile,s.number,1)  =','
end  
go
-- 执行这个过程 
exec sp_xxx 
go
-- 查询表 b 的数据
select * from b 
go
drop proc sp_xxx
go
drop table test , b 
go


(3 行受影响)

(5 行受影响)
mobile                                             content
-------------------------------------------------- ----------
150137447xx                                        验证码
154866256xx                                        456
154841255xx                                        789
189554742xx                                        789
155668484xx                                        789

(5 行受影响)

大神， 我就懒赶上了你， 技术还没你1/100， 执行可用。 非常感谢。

[卖水果的net]

-- 超过 8000 了，试试这个
create table test(mobile varchar(50),  content varchar(10))
go
insert into test values
('150137447xx', '验证码'),
('154866256xx','456'),
('154841255xx,189554742xx,155668484xx','789')
go

SELECT a.content,b.v FROM (
   SELECT t.content,CONVERT(XML,'<n>'+REPLACE(t.mobile,',','</n><n>')+'</n>') c
   FROM test t
) a CROSS APPLY(SELECT n.value('.','varchar(20)') v FROM a.c.nodes('n') k(n)) b
go
drop table test 
go

(3 行受影响)
content    v
---------- --------------------
验证码        150137447xx
456        154866256xx
789        154841255xx
789        189554742xx
789        155668484xx

(5 行受影响)