我也來練手T-SQL寫程式..@@
引用微笑的前段建立...

--建表
DECLARE @COP AS TABLE
(
   [A001] varchar(4),
   [A002] varchar(5),
   [A003] varchar(15)
)

DECLARE @INV AS TABLE
(
   [B001] varchar(15)
)

--放入參考資料
INSERT INTO @COP VALUES('2AA','1111','')
INSERT INTO @COP VALUES('1AA','1111','')
INSERT INTO @COP VALUES('3AA','1110','')
INSERT INTO @COP VALUES('1AA','1112','')

INSERT INTO @INV VALUES('AB00000000')

declare @i int = 0
declare @j int = 0
declare @k int = 0
declare @n int = 0
declare @Count int
declare @data1 varchar(4)
declare @data2 varchar(5)
declare @Head varchar(2)
declare @Body varchar(8)
declare @Str varchar(10)

select @Count=count(0)
from @COP
where isNull([A003],'') = ''

while(@i<@Count)
begin
	select top 1 @data1=[A001]
	,@data2=[A002]
	from @COP
	where isNull([A003],'') = ''

	while(@j<1)
	begin
		select @Head = left([B001],2)
		,@Body=right([B001],8)
		from @INV
        
		set @n = Convert(int,@Body)
        
		select @k=1
		from @COP
		where [A003] = @Head + @Body

		if(@k=0)
		begin
			set @Str = @Head + @Body
			set @j = 1
            
			update @COP
			set [A003] = @Str
			where [A001]=@data1
			and [A002] = @data2

			set @n = @n + 1
			set @Str = @Head + right('0000000' + Convert(varchar(8), @n),8)

			update @INV
			set [B001] = @Str

		end
		else
		begin
			set @n = @n + 1
			set @Str = @Head + right('0000000' + Convert(varchar(8), @n),8)
			update @INV
			set [B001] = @Str
		end
	end

	set @i = @i + 1
	set @j = 0
end

select *
from @COP

select *
from @INV

方法有點笨，之後有其他大神的話最佳請給他們就好。練手而已。

--建表
DECLARE @COP AS TABLE
(
   [A001] varchar(4),
   [A002] varchar(5),
   [A003] varchar(15)
)

DECLARE @INV AS TABLE
(
   [B001] varchar(15)
)

--放入參考資料
INSERT INTO @COP VALUES('2AA','1111','')
INSERT INTO @COP VALUES('1AA','1111','')
INSERT INTO @COP VALUES('3AA','1110','')
INSERT INTO @COP VALUES('1AA','1112','')

INSERT INTO @INV VALUES('AB00000000')

--宣告變數
DECLARE @_B001 varchar(15)
DECLARE @_COPCount int
DECLARE @_i int

SELECT @_B001 = B001 FROM @INV

SELECT @_COPCount = COUNT(*) FROM @COP WHERE ISNULL(A003,'')=''

SET @_i = 0

--新增資料
WHILE(@_i<@_COPCount)
BEGIN
	WITH TEMPCOP AS(
	SELECT TOP 1 * FROM @COP WHERE ISNULL(A003,'')='' ORDER BY A001,A002
	)
	UPDATE TEMPCOP SET A003=@_B001
	SET @_B001 = SUBSTRING(@_B001,0,3)+RIGHT('00000000'+CAST( CAST( SUBSTRING(@_B001,3, LEN(@_B001)) AS INT) + 1 AS VARCHAR(15)),8)
	UPDATE @INV SET B001=@_B001
	SET @_i = @_i + 1
END

/*顯示結果
@COP
2AA	1111	AB00000002
1AA	1111	AB00000000
3AA	1110	AB00000003
1AA	1112	AB00000001

@COP Order by
1AA	1111	AB00000000
1AA	1112	AB00000001
2AA	1111	AB00000002
3AA	1110	AB00000003

@INV
AB00000004
*/
select * from @COP

select * from @COP Order by A001,A002

select * from @INV

................恕刪~~回錯

1.先備份 COP 及 INV 兩個 TABLE
2.假設 COP 的 A003 未曾使用過 AB 開頭的發票

CREATE TABLE COP (
A001 varchar(40) NULL ,
A002 varchar(40) NULL ,
A003 varchar(40) NULL);

INSERT INTO COP (A001,A002,A003)
VALUES 
('1AA','1111',NULL),
('1AA','1112',''),
('2AA','1111',''),
('2AA','1112','XX55555555'),
('3AA','1110','');

CREATE TABLE INV (
B001 varchar(40) NULL);

INSERT INTO INV (B001)
VALUES 
('AB00000000');

BEGIN TRANSACTION;  
-- 更新 COP 發票號碼 : A003
UPDATE A
SET A.A003=B.A003NEW
FROM COP AS A
LEFT JOIN (
SELECT A001,A002,
(SELECT LEFT(B001,2) FROM INV)+RIGHT('00000000'+CONVERT(VARCHAR(8),
(SELECT CONVERT(INT,SUBSTRING(B001,3,8)) FROM INV)+
CONVERT(VARCHAR(8),ROW_NUMBER() OVER (ORDER BY A001,A002)-1)),8) AS A003NEW
FROM COP
WHERE ISNULL(A003,'')=''
) AS B ON B.A001=A.A001 AND B.A002=A.A002
WHERE ISNULL(A.A003,'')='';

-- 更新 INV 已用發票號碼 : B001
UPDATE C
SET C.B001=(SELECT
LEFT(MAX(A003),2)+
RIGHT('00000000'+CONVERT(VARCHAR(8),CONVERT(INT,SUBSTRING(MAX(A003),3,8))+1),8) 
FROM COP
WHERE LEFT(A003,2)=LEFT(C.B001,2))
FROM INV AS C;

COMMIT; 

Demo