insert tb1(ID,buildID,name)
select A,buildID,name
from tb2tb2中数据:
buildID name
a 建筑1
b 建筑2
c 建筑3其中A是由查询得出的buildID + 编号 组成的
编号是一个会自增的数字 比如是2000101插入的就是
ID buildID name
a2000101 a 建筑1
b2000102 b 建筑2
c2000103 c 建筑3求教这个怎么用sql语句或存储过程实现
select A,buildID,name
from tb2tb2中数据:
buildID name
a 建筑1
b 建筑2
c 建筑3其中A是由查询得出的buildID + 编号 组成的
编号是一个会自增的数字 比如是2000101插入的就是
ID buildID name
a2000101 a 建筑1
b2000102 b 建筑2
c2000103 c 建筑3求教这个怎么用sql语句或存储过程实现
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 行受影响)*/
CREATE TABLE tb2
(
buildID VARCHAR(10),
name VARCHAR(10)
)
GOINSERT 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 tb2SELECT * FROM tb1Id buildID name
a2000101 a 建筑1
b2000102 b 建筑2
c2000103 c 建筑3
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
ENDexec sp_InserTable 'a','建筑1'
select * from #temp
a20 a 建筑1
a21 a 建筑1
a201201 1 建筑1
a201202 a 建筑1
a201203 a 建筑1
a201204 a 建筑1
a201205 a 建筑1
a201206 a 建筑1
a201207 a 建筑1
SELECT 'b', '建筑2' UNION
SELECT 'c', '建筑3'select 0 id,* into #tb1 from #tb2declare @id int
declare @buildID varchar(10)
set @id=0
set @buildID=''
update #tb1 set @id=@id+1,id=@id from #tb1CREATE 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
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 tb1ID buildID name
--------------- ------- -------
a2000101 a 建筑1
b2000102 b 建筑2
c2000103 c 建筑3(3 row(s) affected)