SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST_A
(A1 nvarchar(20), A2 nvarchar(20),A3 nvarchar(20),BATCH_FLAG nvarchar(20),CREATE_TIME_STAMP datetime);
-- 第一次寫入 TEST_A
INSERT INTO TEST_A SELECT A1='AAA',A2='10',A3='1111-BBBB',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='AAA',A2='20',A3='1111-BBBB',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='AAA',A2='30',A3='1111-BBBB',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='AAA',A2='40',A3='1111-BBBB',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='AAA',A2='50',A3='1111-BBBB',BATCH_FLAG='0','2020-06-02 00:00:00.000';
--
INSERT INTO TEST_A SELECT A1='NNN',A2='50',A3='1111-BBBB',BATCH_FLAG='0','2020-06-03 00:00:00.000';
INSERT INTO TEST_A SELECT A1='NNN',A2='50',A3='1111-BBBB',BATCH_FLAG='0','2020-06-03 00:00:00.000';
7 rows affected

select *
from TEST_A;


A1
A2
A3
BATCH_FLAG
CREATE_TIME_STAMP


AAA
10
1111-BBBB
0
2020-06-02 00:00:00.000

AAA
20
1111-BBBB
0
2020-06-02 00:00:00.000

AAA
30
1111-BBBB
0
2020-06-02 00:00:00.000

AAA
40
1111-BBBB
0
2020-06-02 00:00:00.000

AAA
50
1111-BBBB
0
2020-06-02 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000


CREATE TABLE TEST_B
(B1 nvarchar(12), B2 nvarchar(20),B3 nvarchar(20),B4 nvarchar(20));
select *
from TEST_B;


B1
B2
B3
B4


-- 特定日期,第一次寫入 TEST_B
INSERT INTO TEST_B (B1,B2,B3,B4) 
SELECT
-- B1
CONVERT (VARCHAR(8), GETDATE(), 112) + 
  RIGHT (CONVERT (VARCHAR (5),10000 + 
        (SELECT isnull(RIGHT (MAX(B1),4),'0000')
         FROM TEST_B
         WHERE LEFT (B1,8) = CONVERT (VARCHAR(8), GETDATE(), 112)) + 
         (ROW_NUMBER () OVER (PARTITION BY BATCH_FLAG ORDER BY BATCH_FLAG))
         ),4
	 ),
-- B2
A1,
-- B3
A2,
-- B4 
A3
FROM TEST_A
WHERE (BATCH_FLAG = '0' or BATCH_FLAG = '')
AND CONVERT (VARCHAR(8), CREATE_TIME_STAMP, 112) = CONVERT (VARCHAR(8), GETDATE(), 112);
5 rows affected

select *
from TEST_B
ORDER BY B1;


B1
B2
B3
B4


202006020001
AAA
10
1111-BBBB

202006020002
AAA
20
1111-BBBB

202006020003
AAA
30
1111-BBBB

202006020004
AAA
40
1111-BBBB

202006020005
AAA
50
1111-BBBB


-- 第一次更新 TEST_A
UPDATE TEST_A
SET BATCH_FLAG='1'
WHERE (BATCH_FLAG = '0' or BATCH_FLAG = '')
AND CONVERT (VARCHAR(8), CREATE_TIME_STAMP, 112) = CONVERT (VARCHAR(8), GETDATE(), 112);
5 rows affected

select *
from TEST_A;


A1
A2
A3
BATCH_FLAG
CREATE_TIME_STAMP


AAA
10
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
20
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
30
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
40
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
50
1111-BBBB
1
2020-06-02 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000


-- 第二次寫入 TEST_A
INSERT INTO TEST_A SELECT A1='BBB',A2='60',A3='CCCC-AAAA',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='BBB',A2='70',A3='CCCC-AAAA',BATCH_FLAG='0','2020-06-02 00:00:00.000';
INSERT INTO TEST_A SELECT A1='BBB',A2='80',A3='CCCC-AAAA',BATCH_FLAG='0','2020-06-02 00:00:00.000';
--
INSERT INTO TEST_A SELECT A1='KKK',A2='80',A3='CCCC-AAAA',BATCH_FLAG='0','2020-06-03 00:00:00.000';
INSERT INTO TEST_A SELECT A1='KKK',A2='80',A3='CCCC-AAAA',BATCH_FLAG='0','2020-06-03 00:00:00.000';
5 rows affected

-- 特定日期,第二次寫入 TEST_B : SQL 同上
INSERT INTO TEST_B (B1,B2,B3,B4) 
SELECT
-- B1
CONVERT (VARCHAR(8), GETDATE(), 112) + 
  RIGHT (CONVERT (VARCHAR (5),10000 + 
        (SELECT isnull(RIGHT (MAX(B1),4),'0000')
         FROM TEST_B
         WHERE LEFT (B1,8) = CONVERT (VARCHAR(8), GETDATE(), 112)) + 
         (ROW_NUMBER () OVER (PARTITION BY BATCH_FLAG ORDER BY BATCH_FLAG))
         ),4
	 ),
-- B2
A1,
-- B3
A2,
-- B4 
A3
FROM TEST_A
WHERE (BATCH_FLAG = '0' or BATCH_FLAG = '')
AND CONVERT (VARCHAR(8), CREATE_TIME_STAMP, 112) = CONVERT (VARCHAR(8), GETDATE(), 112);
3 rows affected

select *
from TEST_B
ORDER BY B1;


B1
B2
B3
B4


202006020001
AAA
10
1111-BBBB

202006020002
AAA
20
1111-BBBB

202006020003
AAA
30
1111-BBBB

202006020004
AAA
40
1111-BBBB

202006020005
AAA
50
1111-BBBB

202006020006
BBB
60
CCCC-AAAA

202006020007
BBB
70
CCCC-AAAA

202006020008
BBB
80
CCCC-AAAA


-- 第二次更新 TEST_A : SQL 同上
UPDATE TEST_A
SET BATCH_FLAG='1'
WHERE (BATCH_FLAG = '0' or BATCH_FLAG = '')
AND CONVERT (VARCHAR(8), CREATE_TIME_STAMP, 112) = CONVERT (VARCHAR(8), GETDATE(), 112);
3 rows affected

select *
from TEST_A;


A1
A2
A3
BATCH_FLAG
CREATE_TIME_STAMP


AAA
10
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
20
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
30
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
40
1111-BBBB
1
2020-06-02 00:00:00.000

AAA
50
1111-BBBB
1
2020-06-02 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000

NNN
50
1111-BBBB
0
2020-06-03 00:00:00.000

BBB
60
CCCC-AAAA
1
2020-06-02 00:00:00.000

BBB
70
CCCC-AAAA
1
2020-06-02 00:00:00.000

BBB
80
CCCC-AAAA
1
2020-06-02 00:00:00.000

KKK
80
CCCC-AAAA
0
2020-06-03 00:00:00.000

KKK
80
CCCC-AAAA
0
2020-06-03 00:00:00.000
A1	A2	A3	CREATE_TIME_STAMP
AAA	10	1111-BBBB	2020-06-02 00:00:00.000
AAA	20	1111-BBBB	2020-06-02 00:00:00.000
AAA	30	1111-BBBB	2020-06-02 00:00:00.000
AAA	40	1111-BBBB	2020-06-02 00:00:00.000
AAA	50	1111-BBBB	2020-06-02 00:00:00.000
NNN	50	1111-BBBB	2020-06-03 00:00:00.000
NNN	50	1111-BBBB	2020-06-03 00:00:00.000
B1	B2	B3	B4
202006020001	AAA	10	1111-BBBB
202006020002	AAA	20	1111-BBBB
202006020003	AAA	30	1111-BBBB
202006020004	AAA	40	1111-BBBB
202006020005	AAA	50	1111-BBBB