难搞的Insert INTO，求大虾帮忙

[fan22176391]

USE TEST
GO

CREATE TABLE #Test1(ID INT , BOX CHAR(2),Product CHAR(1),QTY INT )
INSERT #Test
SELECT 1,'01','A',2
UNION ALL
SELECT 1,'01','B',3
UNION ALL
SELECT 1,'02','C',2
UNION ALL
SELECT 2,'06','M',2
UNION ALL
SELECT 2,'09','N',1


CREATE TABLE #Test2 (ID INT ,BOX CHAR(2),Product CHAR(1),ScanNumber CHAR(20))


目的：将#Test1的数据插入#Test2, #Test2中的ScanNumber表示唯一标识符,逻辑为同一个ID号+Box+Product+同一Product数量的递增

插入#Test2呈现结果为：

1,'01','A','1-01-A-01'
1,'01','A','1-01-A-02'
1,'01','B','1-01-B-01'
1,'01','B','1-01-B-02'
1,'01','B','1-01-B-03'
1,'02','C','1-02-C-01'
1,'02','C','1-02-C-02'
2,'06','M','2-06-M-01'
2,'06','M','2-06-M-02'
2,'06','N','2-09-N-01'

不知道各位大侠明白我的意思不？谢谢各位提供下你们的宝贵方法

[快溜]

CREATE TABLE #Test1(ID INT , BOX CHAR(2),Product CHAR(1),QTY INT )
INSERT #Test1
SELECT 1,'01','A',2
UNION ALL
SELECT 1,'01','B',3
UNION ALL
SELECT 1,'02','C',2
UNION ALL
SELECT 2,'06','M',2
UNION ALL
SELECT 2,'09','N',1

CREATE TABLE #Test2 (ID INT ,BOX CHAR(2),Product CHAR(1),ScanNumber CHAR(20))

insert into #Test2
 select ID,BOX,Product,
        RTRIM(id)+'-'+BOX+'-'+Product+'-'+RIGHT('00'+RTRIM(QTY),2) from #Test1
        
select * from #Test2        

/*
ID          BOX  Product ScanNumber
----------- ---- ------- --------------------
1           01   A       1-01-A-02           
1           01   B       1-01-B-03           
1           02   C       1-02-C-02           
2           06   M       2-06-M-02           
2           09   N       2-09-N-01           

(5 行受影响)

[快溜]

CREATE TABLE #Test1(ID INT , BOX CHAR(2),Product CHAR(1),QTY INT )
INSERT #Test1
SELECT 1,'01','A',2
UNION ALL
SELECT 1,'01','B',3
UNION ALL
SELECT 1,'02','C',2
UNION ALL
SELECT 2,'06','M',2
UNION ALL
SELECT 2,'09','N',1

CREATE TABLE #Test2 (ID INT ,BOX CHAR(2),Product CHAR(1),ScanNumber CHAR(20))

insert into #Test2
 select ID,BOX,Product,
        RTRIM(id)+'-'+BOX+'-'+Product+'-'+RIGHT('00'+RTRIM(b.number+1),2) 
   from #Test1 a,master..spt_values b where b.type='p' and b.number<a.QTY
        
select * from #Test2        

/*
ID          BOX  Product ScanNumber
----------- ---- ------- --------------------
1           01   A       1-01-A-01           
1           01   A       1-01-A-02           
1           01   B       1-01-B-01           
1           01   B       1-01-B-02           
1           01   B       1-01-B-03           
1           02   C       1-02-C-01           
1           02   C       1-02-C-02           
2           06   M       2-06-M-01           
2           06   M       2-06-M-02           
2           09   N       2-09-N-01           

(10 行受影响)

[叶子]

CREATE TABLE #Test1(ID INT , BOX CHAR(2),Product CHAR(1),QTY INT )
INSERT #Test1
SELECT 1,'01','A',2
union all
SELECT 1,'01','A',2
UNION ALL
SELECT 1,'01','B',3
UNION ALL
SELECT 1,'02','C',2
UNION ALL
SELECT 2,'06','M',2
UNION ALL
SELECT 2,'09','N',1

CREATE TABLE #Test2 (ID INT ,BOX CHAR(2),Product CHAR(1),ScanNumber CHAR(20))

insert into #Test2(ID,BOX,Product)
select ID,BOX,Product from #Test1

;with t as
(
select *,row_number() over (partition by ID,BOX,Product order by ID) as rid from #Test2
)

select ID,BOX,Product,
[ScanNumber]=ltrim(ID)+'-'+BOX+'-'+Product+'-'+right('00'+ltrim(rid),2) 
from t
/*
ID          BOX  Product ScanNumber
----------- ---- ------- ----------------------
1           01   A       1-01-A-01
1           01   A       1-01-A-02
1           01   B       1-01-B-01
1           02   C       1-02-C-01
2           06   M       2-06-M-01
2           09   N       2-09-N-01
*/


drop table #Test1,#Test2

[--小F--]

CREATE TABLE #Test1(ID INT , BOX CHAR(2),Product CHAR(1),QTY INT )
INSERT #Test1
SELECT 1,'01','A',2
UNION ALL
SELECT 1,'01','B',3
UNION ALL
SELECT 1,'02','C',2
UNION ALL
SELECT 2,'06','M',2
UNION ALL
SELECT 2,'09','N',1

CREATE TABLE #Test2 (ID INT ,BOX CHAR(2),Product CHAR(1),ScanNumber CHAR(20))

insert into
   #Test2
select
 ID,BOX,Product,
 ltrim(id)+'-'+BOX+'-'+Product+'-'+RIGHT('00'+ltrim(b.number+1),2) 
from
 #Test1 a,
 master..spt_values b 
where
 b.type='p' and b.number<a.QTY
        
select * from #Test2        

drop table #Test1,#Test2
go

/*ID          BOX  Product ScanNumber
----------- ---- ------- --------------------
1           01   A       1-01-A-01           
1           01   A       1-01-A-02           
1           01   B       1-01-B-01           
1           01   B       1-01-B-02           
1           01   B       1-01-B-03           
1           02   C       1-02-C-01           
1           02   C       1-02-C-02           
2           06   M       2-06-M-01           
2           06   M       2-06-M-02           
2           09   N       2-09-N-01           

(10 行受影响)*/