[資料表設計] 若insert新資料前，要檢查原資料表內是否已有「同一組」的資料

ms sql sql schema

wu2960 2021-07-08 14:23:33 ‧ 1470 瀏覽

請教大家一個資料表規劃的問題，有一情境:

一筆資料新增到目的地資料表(@Target)之前，要先檢查編號(no)是否已存在，
沒有就直接新增，有就判斷欲插入的Value是否與已存在的Value同一組；

而判斷Value是否同組是不是要用參考Table(@RefTable)來記錄是否屬於同一組?
像語法中的@RefTable ([Value] char(1), [Group] char(1))這樣?
還是說有更適合的做法?

MSSQL語法如下:

--要插入的目的地Table
DECLARE @Target TABLE ([no] char(3), [Value] char(1))
INSERT INTO @Target ([no], [Value]) SELECT '101','A'
INSERT INTO @Target ([no], [Value]) SELECT '102','B'
INSERT INTO @Target ([no], [Value]) SELECT '103','D'
INSERT INTO @Target ([no], [Value]) SELECT '104','F'
INSERT INTO @Target ([no], [Value]) SELECT '105','G'
INSERT INTO @Target ([no], [Value]) SELECT '106','J'
SELECT * FROM @Target

--欲插入的data
SELECT '107','A' --@Target沒有107 => 可插入
SELECT '101','C' --@Target有101，但C與A是同一個Group => 不可插入
SELECT '102','C' --@Target有102，且C與B是不同Group => 可插入

--分組參考的Table
DECLARE @RefTable TABLE ([Value] char(1), [Group] char(1))

INSERT INTO @RefTable ([Value],[Group]) SELECT 'A', '1'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'B', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'C', '1'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'D', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'E', '3'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'F', '3'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'G', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'H', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'I', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'J', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'K', '1'

SELECT * FROM @RefTable

我會有疑問的地方是預計的@RefTable大概會有3千多筆，這樣去分類(Group)編號，會不會編到最後自己亂了?

登入發表討論

直播研討會

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

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2021-07-08 15:25:57

最佳解答

新增前可以先判斷是否已存在~
已在更新內容寫法

DECLARE @RefTable TABLE ([Value] char(1), [Group] char(1))

INSERT INTO @RefTable ([Value],[Group]) SELECT 'A', '1'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'B', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'C', '1'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'D', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'E', '3'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'F', '3'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'G', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'H', '2'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'I', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'J', '4'
INSERT INTO @RefTable ([Value],[Group]) SELECT 'K', '1'


--SELECT * FROM @RefTable

DECLARE @Target TABLE ([no] char(3), [Value] char(1))
INSERT INTO @Target ([no], [Value]) SELECT '101','A'
INSERT INTO @Target ([no], [Value]) SELECT '102','B'
INSERT INTO @Target ([no], [Value]) SELECT '103','D'
INSERT INTO @Target ([no], [Value]) SELECT '104','F'
INSERT INTO @Target ([no], [Value]) SELECT '105','G'
INSERT INTO @Target ([no], [Value]) SELECT '106','J'
--SELECT * FROM @Target

INSERT INTO @Target ([no], [Value]) 
SELECT '107','A' 
where not exists(
	select 0
	from @RefTable a
	left join @Target b on a.Value = b.Value
	where a.[Group] in(
		select c.[Group]
		from @RefTable c
		where c.[Value] = 'A'
	)
	and b.no = '107'
)

INSERT INTO @Target ([no], [Value]) 
SELECT '101','C' 
where not exists(
	select 0
	from @RefTable a
	left join @Target b on a.Value = b.Value
	where a.[Group] in(
		select c.[Group]
		from @RefTable c
		where c.[Value] = 'C'
	)
	and b.no = '101'
)

INSERT INTO @Target ([no], [Value]) 
SELECT '102','C' 
where not exists(
	select 0
	from @RefTable a
	left join @Target b on a.Value = b.Value
	where a.[Group] in(
		select c.[Group]
		from @RefTable c
		where c.[Value] = 'C'
	)
	and b.no = '102'
)

SELECT * FROM @Target