insert into表itemno自动添加,该如何处理

insert into表itemno自动添加
SQL2000, billid相同时,在原表itemno里面加入顺序号
原表 mnf_mrpprogramdetail
billid,itemno,materialid,billtype,remark
1001  ,  1   , 203      ,'' ,''
1001  ,  2   , 208      ,'' ,''
1001  ,  3   , 301      ,'' ,''
1003  ,  1   , 332      ,'' ,''
1003  ,  2   , 337      ,'' ,''


表Mnf_prdpf (注:这个表的数据只是临时数据,随时会清空的)
referbillid,referitemno,materialid,billtype,remark
1001 ,  1    , 901      ,''  ,''
1001 ,  2    , 902      ,''  ,''
1001 ,  3    , 903      ,''  ,''
1003  ,  7   , 339      ,'' ,''
1003  ,  8   , 338      ,'' ,''

要求结果 UPDATE 
原表 mnf_mrpprogramdetail
billid,itemno,materialid,billtype,remark
1001  ,  1   , 203      ,'' ,''
1001  ,  2   , 208      ,'' ,''
1001  ,  3   , 301      ,'' ,''
1001  ,  4   , 901      ,''  ,''
1001  ,  5   , 902      ,''  ,''
1001  ,  6   , 903      ,''  ,''
1003  ,  1   , 332      ,'' ,''
1003  ,  2   , 337      ,'' ,''
1003  ,  3   , 339      ,'' ,''
1003  ,  4   , 338      ,'' ,''


------解决方案--------------------
操作前先备份一下，以免出问题

----------------------------------------------------------------

-- Author  :DBA_Huangzj(發糞塗牆)

-- Date    :2014-01-14 10:15:03

-- Version:

--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 

--	Dec 28 2012 20:23:12 

--	Copyright (c) Microsoft Corporation

--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

--

----------------------------------------------------------------

--> 测试数据：[mnf_mrpprogramdetail]

if object_id('[mnf_mrpprogramdetail]') is not null drop table [mnf_mrpprogramdetail]

go 

create table [mnf_mrpprogramdetail]([billid] int,[itemno] int,[materialid] int,[billtype] varchar(1),[remark] varchar(1))

insert [mnf_mrpprogramdetail]

select 1001,1,203,'','' union all

select 1001,2,208,'','' union all

select 1001,3,301,'','' union all

select 1003,1,332,'','' union all

select 1003,2,337,'',''

--> 测试数据：[Mnf_prdpf]

if object_id('[Mnf_prdpf]') is not null drop table [Mnf_prdpf]

go 

create table [Mnf_prdpf]([referbillid] int,[referitemno] int,[materialid] int,[billtype] varchar(1),[remark] varchar(1))

insert [Mnf_prdpf]

select 1001,1,901,'','' union ALL

select 1001,2,902,'','' union all

select 1001,3,903,'','' union all

select 1003,7,339,'','' union all

select 1003,8,338,'',''

--------------开始查询--------------------------

IF OBJECT_ID('tempdb..#t','u')IS NOT NULL 

DROP TABLE #t

go

IF OBJECT_ID('tempdb..#t2','u')IS NOT NULL 

DROP TABLE #t2

go

SELECT billid,itemno,materialid,billtype,remark INTO #t

FROM (

select * from [mnf_mrpprogramdetail] 

UNION ALL 

SELECT * FROM  [Mnf_prdpf]  )a


SELECT billid,(SELECT COUNT(1) FROM #t b WHERE a.billid=b.billid AND a.itemno>b.itemno)+1 itemno,materialid,billtype,remark INTO #t2

FROM #t a

ORDER BY billid,itemno


--更新已有的

UPDATE [mnf_mrpprogramdetail]

SET [mnf_mrpprogramdetail].itemno=b.itemno

FROM #t2 b 

WHERE [mnf_mrpprogramdetail].billid=b.billid AND [mnf_mrpprogramdetail].materialid=b.materialid


--插入新的

INSERT INTO [mnf_mrpprogramdetail]

SELECT * FROM #t2 a

WHERE NOT EXISTS (SELECT 1 FROM [mnf_mrpprogramdetail] b WHERE a.billid=b.billid AND a.materialid=b.materialid

)


SELECT * FROM [mnf_mrpprogramdetail]

----------------结果----------------------------