需要把查询出来的结果指定格式插入到一张新表中去，请专家解答

Question

查询出来的结果指定格式插入到一张新表中去

 
 DROP TABLE  #Tbb5
 SELECT * INTO #Tbb5 FROM(
select '梦想'as name,'小镇'as num,'健康新名单'Rvalue
)t

DROP TABLE  #Tbb6
 SELECT * INTO #Tbb6 FROM(
select '4004'notext,'1'Nr
)t
----->问题：执行以下代码，查询出来的结果指定格式插入到一张新表中去
 ---建表
create table new_name 
(
name varchar(50)
)
go
--动态sql添加数据
insert into new_name
exec('select 1')

go
--存储过程
create proc pronew_name
as
DECLARE @sql nvarchar(max)
SELECT @sql=N'select '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+' from(SELECT TOP ((SELECT MAX(nr)+1 FROM #tbb6)) t.[NoText] as
 '+QUOTENAME(T4.name+t4.num+t4.Rvalue)+N' ,Nr  FROM #tbb6 AS t 
  union SELECT ''请登记编号1# (以便核对)'',-1 )t order by t.Nr' FROM #tbb5 AS T4
EXEC(@sql) 
go

--存储过程添加数据
insert into new_name
exec pronew_name

select * from new_name
-----------------------------------------------------------------------------
查询的结果

> name
1
请登记编号1# (以便核对)
4004

实际 想要的结果

梦想小镇健康新名单
请登记编号1# (以便核对)
4004

Answer 1

要作为内容的话就不应该当成标题

--存储过程
create proc pronew_name
as
DECLARE @sql nvarchar(max)
SELECT @sql=N'select notext from(select name+num+Rvalue notext,0 as nr from #tbb5 
              union SELECT ''请登记编号1# (以便核对)'',1 
              union select notext,2 from #tbb6) t order by nr'
EXEC(@sql) 
go