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]
----------------结果----------------------------
/*
billid itemno materialid billtype remark
----------- ----------- ----------- -------- ------
1001 1 203
1001 3 208
1001 5 301
1003 1 332
1003 2 337
1001 1 901
1001 3 902
1001 5 903
1003 3 339
1003 4 338
*/