--> 测试数据：[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 all
select 'b','建筑2' union all
select 'c','建筑3'
--------------开始查询--------------------------

select *,[buildID]+right(2000100+ltrim(row_number()over(order by [buildID])),7) from [ta]
----------------结果----------------------------
/* 
buildID name  
------- ----- -------------
a       建筑1   a2000101
b       建筑2   b2000102
c       建筑3   c2000103

(3 行受影响)

*/

------解决方案--------------------

SQL code

CREATE TABLE tb2
(
    buildID VARCHAR(10),
    name VARCHAR(10)
)
GO

INSERT INTO tb2
SELECT 'a', '建筑1' UNION
SELECT 'b', '建筑2' UNION
SELECT '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,name
from tb2

SELECT * FROM tb1

Id    buildID    name
a2000101    a    建筑1
b2000102    b    建筑2
c2000103    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 int
set @currentid = (select max(cast(substring([ID],len(@buildid)+1,10) as int)) from #temp)
if(@currentid is null) set @currentid = 1
set @currentid  = @currentid+1
insert #temp(ID,buildID,name)values(@buildid +cast(@currentid as varchar(20)),@buildid,@name)
Error:
SET NOCOUNT OFF 
END

exec 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

-- 建表tb1
create table tb1(ID varchar(15),buildID varchar(2),name varchar(7))

-- 建表tb2
create table tb2(buildID varchar(2),name varchar(7))

insert into tb2
select 'a', '建筑1' union all
select 'b', '建筑2' union all
select 'c', '建筑3'

-- 建存储过程
create proc instb1
as
begin
  insert tb1(ID,buildID,name)
  select buildID+cast(2000100+row_number() over(order by getdate()) as varchar) 'ID',
         buildID,
         name
  from tb2
end

-- 执行
exec instb1

-- 结果
select * from tb1

ID              buildID name
--------------- ------- -------
a2000101        a       建筑1
b2000102        b       建筑2
c2000103        c       建筑3

(3 row(s) affected)