DB Foreach 將A table 資料丟入 B和C table

microsoft sql sql2012

魚仔 2020-06-01 13:38:19 ‧ 872 瀏覽

分享至

hi 各位大神安安

目前在寫Microsoft的 procdure SQL code
想請問一下

現有一個A table 需要將
batch_flag ='0' 的資料列的資料丟入 (不只一筆)

B和C table
B和C table 會有自己的PK key
其PK_key 是由 [申請日期(4)+月(2)+日(2)+流水號(4)]自己塞入產生
當天如果202006010002了
下次要從202006010003開始
但隔天跑時要變成202006020001 開始
B和C table 也有一些欄位需要自塞預設值

迴圈塞完資料後
需要將A table batch_flag = '1' 代表跑完 ><|||

目前在電腦前只大約這樣的code QQ
--算出當日列數
declare @TodayRowNum int;
select @TodayRowNum=COUNT (CREATE_TIME_STAMP) from [DigitalExamine]
where DateDiff(dd,CREATE_TIME_STAMP,getdate())=0
--print (@TodayRowNum)

declare @TodayDate varchar(15);
select @TodayDate = Cast(Year(GetDate()) As Varchar(4))+Replicate('0',2-Len(Month(GetDate())))+Cast(Month(GetDate()) As Varchar(2))+Replicate('0',2-Len(Day(GetDate())))+Cast(Day(GetDate()) As Varchar(2))
--print (@TodayDate)
--產生流水編號
declare @TodayDateNum varchar(15);
select @TodayDateNum = @TodayDate+Replicate('0',4-Len(@TodayRowNum+1))+Cast((@TodayRowNum+1) as Varchar(4));
print (@TodayDateNum)

--選出未做批次的
if(select * from [DigitalAccount]
where BATCH_FLAG = '0' or BATCH_FLAG = '' )> 0

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2020-06-01 18:55:52

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';

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

-- 寫入 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);

-- 更新 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);

Demo