全部分了，求T-SQL语句
tb1
ID 标识 数量 单号
1 A 1000 CN1
2 B 200 CN2
3 B 100 CN1
4 C 200 CN3
怎么根据TB1中的数据在TB2中插入数据
TB2
ID 序列号 单号
1 A00001 CN1
2 A00002 CN1
3 A00003 CN1
...
...
.
按TB1数量插入1000个，然后接着插入B
B00001 CN2
B00002 CN2
... 插入单号为CN2的数量200
..
B00200 CN2
后面继续插入单号为CN1的数量100，但是由于标识相同，要求序列号要延续，
B00201 CN1
B00202 CN1
....
....
B00300 CN1
C00001 CN3
.....
...
序列号的生成规律要求能参照此帖
http://bbs.csdn.net/topics/390536906
不知道我表述清楚了没，求高手回复，谢谢！
[解决办法]
--为什么楼主上一帖是无满意结帖呢
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] INT, [标识] varchar(100), [数量] INT, [单号] varchar(100));
insert #temp
select '1','A','1000','CN1' union all
select '2','B','200','CN2' union all
select '3','B','100','CN1' union all
select '4','C','200','CN3'
CREATE TABLE #tempB(id INT, 序列号 VARCHAR(100), 单号 VARCHAR(100))
--SQL:
;WITH
cte1 AS(SELECT num = 1 UNION ALL SELECT 1),
cte2 AS(SELECT num = 1 FROM cte1 a, cte1 b),
cte3 AS(SELECT num = 1 FROM cte2 a, cte2 b),
cte4 AS(SELECT num = 1 FROM cte3 a, cte3 b),
cte5 AS(SELECT num = 1 FROM cte4 a, cte4 b),
cteNum as(SELECT num = ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte5),
Data AS
(
select *,beginno=ISNULL((SELECT SUM([数量]) FROM #temp b WHERE b.[标识]=A.[标识] AND B.ID < A.ID),0)+1
from #temp A
)
INSERT INTO #tempB(id, 序列号, 单号)
SELECT A.id, a.[标识]+RIGHT('00000'+RTRIM(B.num), 5), A.[单号]
FROM Data A
INNER JOIN cteNum B
ON B.num BETWEEN A.beginno AND (a.beginno+a.数量-1)
SELECT * FROM #tempB
--结果自己看:
if OBJECT_ID('tempdb..#temp1', 'u') is not null drop table #temp1;
create table #temp1( [ID] INT, [标识] varchar(100), [数量] INT, [单号] varchar(100));
insert #temp1
select '1','A','1000','CN1' union all
select '2','B','200','CN2' union all
select '3','B','100','CN1' union all
select '4','C','200','CN3'
if OBJECT_ID('tempdb..#temp2', 'u') is not null drop table #temp2;
CREATE TABLE #temp2(id INT, [序列号] VARCHAR(100), [单号] VARCHAR(100))
;
WITH a1 AS
(
SELECT *,ISNULL((SELECT SUM([数量]) FROM #temp1 WHERE [标识]=a.[标识] AND id<a.id),0) lj_qty
FROM #temp1 a
)
,a2 AS
(
SELECT [ID],[标识],[数量],[单号],1 n,lj_qty+1 n2 FROM a1
UNION ALL
SELECT [ID],[标识],[数量],[单号],1+n,1+n2 FROM a2
WHERE n<[数量]
)
INSERT #temp2
SELECT ROW_NUMBER() OVER (ORDER BY id,n2) id,
[标识]+CHAR((n2-1)/10000+65)+RIGHT('000'+CONVERT(VARCHAR(4),n2-(n2-1)/10000*10000-1),4) [序列号],
[单号]
FROM a2
OPTION(MAXRECURSION 999)
SELECT * FROM #temp2