请问 查询结果中的一列加上自增编号再插入到一个表中

请教 查询结果中的一列加上自增编号再插入到一个表中
insert tb1(ID,buildID,name)
select A,buildID,name
from tb2

tb2中数据:
buildIDname
a建筑1
b建筑2
c建筑3

其中A是由查询得出的buildID + 编号 组成的
编号是一个会自增的数字 

比如是2000101

插入的就是
IDbuildIDname
a2000101a建筑1
b2000102b建筑2
c2000103c建筑3

求教这个怎么用sql语句或存储过程实现

[解决办法]

SQL code

--> 测试数据：[ta]if object_id('[ta]') is not null drop table [ta]go create table [ta]([buildID] varchar(1),[name] varchar(5))insert [ta]select 'a','建筑1' union allselect 'b','建筑2' union allselect 'c','建筑3'--------------开始查询--------------------------select *,[buildID]+right(2000100+ltrim(row_number()over(order by [buildID])),7) from [ta]----------------结果----------------------------/* buildID name  ------- ----- -------------a       建筑1   a2000101b       建筑2   b2000102c       建筑3   c2000103(3 行受影响)*/
[解决办法]
SQL code
CREATE TABLE tb2(    buildID VARCHAR(10),    name VARCHAR(10))GOINSERT INTO tb2SELECT 'a', '建筑1' UNIONSELECT 'b', '建筑2' UNIONSELECT 'c', '建筑3'CREATE TABLE tb1(    Id VARCHAR(100),    buildID VARCHAR(10),    name VARCHAR(10))insert tb1(ID,buildID,name)select buildID + CAST((2000100 + ROW_NUMBER() OVER (ORDER BY buildID)) AS VARCHAR(100)),buildID,namefrom tb2SELECT * FROM tb1Id    buildID    namea2000101    a    建筑1b2000102    b    建筑2c2000103    c    建筑3
[解决办法]
SQL code
Create Table #temp([ID] varchar(20) not null,buildID varchar(20) not null,name varchar(20) null)create proc sp_InserTable @buildid varchar(20),@name varchar(20)AS BEGIN SET NOCOUNT ON declare @currentid intset @currentid = (select max(cast(substring([ID],len(@buildid)+1,10) as int)) from #temp)if(@currentid is null) set @currentid = 1set @currentid  = @currentid+1insert #temp(ID,buildID,name)values(@buildid +cast(@currentid as varchar(20)),@buildid,@name)Error:SET NOCOUNT OFF ENDexec sp_InserTable 'a','建筑1'select * from #temp
[解决办法]
SELECT 'a' buildID, '建筑1' name into #tb2 UNION
SELECT 'b', '建筑2' UNION
SELECT 'c', '建筑3'

select 0 id,* into #tb1 from #tb2

declare @id int
declare @buildID varchar(10)
set @id=0
set @buildID=''  
update #tb1 set @id=@id+1,id=@id from #tb1

CREATE TABLE tb
(
   Id VARCHAR(100),
   buildID VARCHAR(10),
   name VARCHAR(10)
)

insert into tb
select buildID+cast(2000100+id as varchar(10)) id,buildID,name from #tb1
[解决办法]
用存储过程实现,
SQL code
-- 建表tb1create table tb1(ID varchar(15),buildID varchar(2),name varchar(7))-- 建表tb2create table tb2(buildID varchar(2),name varchar(7))insert into tb2select 'a', '建筑1' union allselect 'b', '建筑2' union allselect 'c', '建筑3'-- 建存储过程create proc instb1asbegin  insert tb1(ID,buildID,name)  select buildID+cast(2000100+row_number() over(order by getdate()) as varchar) 'ID',         buildID,         name  from tb2end-- 执行exec instb1-- 结果select * from tb1ID              buildID name--------------- ------- -------a2000101        a       建筑1b2000102        b       建筑2c2000103        c       建筑3(3 row(s) affected)