How to selectively [double] insert (T-SQL) use a single command?

This is a question for an SQL expert. I am using SQL Server 2008 R2

I have two corresponding tables: Labs and LabUsers .

Users are assigned Labs without repeating entire groups of any order.

The goal is to insert @userName (for example, @user = "Paul" ) in LabUsers , fulfilling all of the following restrictions:

  • In group no more than @maxUsers (for example @maxUsers=4 )

  • No duplicates of complete groups (full laboratories). The order of users in the group is negligible. [edit]

  • If no existing Lab is allowed, create ( INSERT ) a new lab, then insert the line for @user if @user is not specified (for example @maxLabs=5 ).

  • Very important . There are many parallel identical requests from the server in a split second, which can interfere with each other. Therefore, as soon as a command begins to be executed, no other requests can be executed until the end of this command.

  • The query should return 0 in cases where it cannot fulfill the above restrictions, and returns the LabID inserted row.

  • [EDITED] There are several zones labs. The zones are independent. Each #labCount zone is limited to @maxLabs . The value of @maxLabs is equal for all zones, therefore Total_maxLabs = @maxLabs x #zonesCount . For example, @zone=51 (later @zone=52, 53 etc. ). (The same LabUsers can use zones without restrictions. Zones do not know about each other)

  • LabID in LabUsers is a foreign key from Labs .

Example:

Here is the Labs table:

 LabID LabName LabZone ----- ------- ------- 1 North 51 2 North East 51 3 South West 51 

And LabUsers :

 LabUserID LabUserName LabID --------- ----------- ----- 1 Diana 3 2 Julia 2 3 Paula 2 4 Romeo 1 5 Julia 3 6 Rose 2 7 Diana 1 8 Diana 2 9 Julia 1 10 Romeo 3 11 Paul 1 

In the example, users are assigned as follows:

 LabID LabName LabZone LabUsers (ordered LTR a>z) ----- ------- ------- -------- 1 North 51 Diana•Julia•Paul•Romeo 2 North East 51 Diana•Julia•Paula•Rose 3 South West 51 Diana•Julia•Romeo 
  • The insert should not be in LabID=1 or 2, because there are already 4 users in these labs.
  • The insertion should not take place in LabID=3 due to the creation of a duplicate with LabID=1 .

Therefore, since @maxLabs not 3 (existing laboratories), you must insert a new line in Labs with the value LabZone=@zone =51 .

IDENTITY sets LabID to 4 for a new line.

It's time to insert Paul into LabUsers with the LabID just returned from the new lab insert.

How to solve this problem?

What is the method used to ensure that the command is executed as a whole without interference?

script to create the database:

 CREATE DATABASE [Allocation] GO USE [Allocation] GO CREATE TABLE [dbo].[LabUsers]( [LabUserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , [LabUserName] [nvarchar](50) NOT NULL, [LabID] [int] NOT NULL) GO SET IDENTITY_INSERT [dbo].[LabUsers] ON INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (1, N'Diana', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (2, N'Julia', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (3, N'Paula', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (4, N'Romeo', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (5, N'Julia', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (6, N'Rose', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (7, N'Diana', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (8, N'Diana', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (9, N'Julia', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (10, N'Romeo', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (11, N'Paul', 1) SET IDENTITY_INSERT [dbo].[LabUsers] OFF CREATE TABLE [dbo].[Labs]( [LabID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , [LabName] [nvarchar](50) NULL, [LabZone] [int] NOT NULL) GO SET IDENTITY_INSERT [dbo].[Labs] ON INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (1, N'North', 51) INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (2, N'North East', 51) INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (3, N'South West', 51) SET IDENTITY_INSERT [dbo].[Labs] OFF 
+4
source share
3 answers

I piggy rejected various variables and implemented a similar but different solution. He assumes that the new laboratory will be 1 more than the maximum available current laboratory. I also make the assumption that the lab does NOT delete users.

The purpose of this solution is to see what the end result of the user’s insertion will look like and run a check on it to see which end result is valid. The logic is this:

  • Get an affordable lab to insert into
    • Verify that the user is not working in the lab.
    • Make sure the lab is not full here.
    • Include a new lab opportunity here
  • Create a list of all laboratory users for each laboratory, sorted alphabetically if the laboratory is full after the user is inserted
    • potential new labs marked
  • Compare labeled lab lists and unfilled lab lists and select the minimum labId, which is not a duplicate of the existing full lab list.
  • Return LabId inserted at or 0 as output

Given the raw data from the original question and execution in the following order:

  • Insert @userName = "Gender", @labZone = 51
    • Gender is added to the newly created Lab 4
  • Insert @userName = "Gender", @labZone = 51
    • Sex is being added to the newly created Lab 5
  • Insert @userName = "Gender", @labZone = 51
    • There are no more new laboratories and there are no existing laboratories for Paul to go so that he returns 0
  • Insert @userName = "Rose", @labZone = 51
    • Rose is added to the existing Laboratory 3
  • Insert @userName = "Rose", @labZone = 51
    • Rose is added to an existing laboratory 4
  • Insert @userName = "Rose", @labZone = 51
    • Rose is added to the existing laboratory 5

A tab in a transaction on LabUsers should prevent concurrent transactions from chaos.

In addition, when debugging common table expressions, it helps replace them with a temporary table so that you can see the results of each step along the way.

 BEGIN TRAN DECLARE @maxUsers INT DECLARE @maxLabs INT DECLARE @userName VARCHAR(50) DECLARE @labZone INT DECLARE @labID INT SET @maxUsers = 4 SET @maxLabs = 5 SET @userName = 'Paul' SET @labZone = 52 SET @labID = NULL declare @currentLabCount int -- get current number of labs select @currentLabCount = count(*) from Labs l /* -- uncomment this if the max labs applies individual lab zones rather than across all lab zones where LabZone = @labZone */ ;with availableLabs as ( -- get available labs to insert into -- check existing labs for valid spots select lu.LabID , count(*) + 1 as LabUserCount -- need this to see when we're at max users from LabUsers lu with (tablockx) -- ensures blocking until this completes (serialization) inner join Labs l with (tablockx) -- might as well lock this too on l.LabId = lu.LabID and l.LabZone = @labZone -- check Lab Zone where not exists( -- make sure lab user isn't already in this lab select 1 from LabUsers lu2 where lu2.LabId = lu.LabId and lu2.LabUserName = @userName ) group by lu.LabID having count(*) < @maxUsers -- make sure lab isn't full union all -- create new lab if not at limit select max(LabId) + 1 as LabId , 1 as LabUserCount from Labs -- check all labs where @currentLabCount < @maxLabs -- don't bother checking new labs if going to exceed max allowable labs ) -- only do this check if lab is going to be filled , dupeCheck as( -- generates a lab user list sorted alphabetically by lab user name per lab select y.LabId , max(y.newLabFlag) as newLabFlag -- if existing lab getting new lab user, then 1, if new lab with new lab user, then 1 else 0 , replace(replace(replace(stuff( -- cool way to comma concatenate without looping/recursion taking advantage of "XML path" ( select ',' + x.LabUserName + '' -- lab users from ( select LabId , @userName as LabUserName from availableLabs -- the new user and his/her potential labs union all select lu.LabId , lu.LabUserName from LabUsers lu -- the current lab users and the labs they belong to ) x where x.LabID = y.LabId -- make sure the LabId match and max(y.LabUserCount) = @maxUsers -- don't generate this list if lab is not full order by x.LabUserName -- sorted alphabetically for xml path('') ), 1, 1, '' ) , '&lt;', '<'), '&gt;', '>'), '&amp;', '&') as LabUserList from ( -- get list of old labs and flag them as such select lu.LabId , convert(tinyint,0) as newLabFlag , count(*) as LabUserCount -- need the current lab user count from LabUsers lu /* -- uncomment this if full labs can be duplicated across lab zones inner join Labs l on l.LabId = lu.LabId and l.LabZone = @labZone */ group by lu.LabId union all -- get list of potential candidate labs for lab user and flag them as such select al.LabId , convert(tinyint,1) as newLabFlag , al.LabUserCount -- new lab user count if we were to insert the new user from availableLabs al ) y group by y.LabId ) select @labID = min(dc.LabID) from dupeCheck dc where dc.newLabFlag = 1 -- make sure the same list of users does not already exist at an existing lab and not exists( select 1 from dupeCheck dupe where dupe.LabUserList = dc.LabUserList and dupe.newLabFlag = 0 ) -- insert new lab if doesn't exist insert into Labs(LabName, LabZone) -- always better to be clearer select 'New Lab' as LabName , @labZone as LabZone where @currentLabCount < @maxLabs -- make sure we can't have more than max labs and not exists( select 1 from Labs where LabId = @labId ) -- insert lab users insert into LabUsers(LabUserName, LabId) select @userName as LabUserName , @labId as LabId where @labId is not null -- return labId select isnull(@labId,0) commit tran 
+1
source

Since MERGE cannot be used, several operators are required. Sorry, I could not come up with a simpler solution. I am sure that an expert can find a better solution.

First, I searched for potential Labs based on these rules. To avoid duplication of groups, I compared users for each laboratory before and after possible insertions. If any lab is available, insert the user. If not, insert the lab, and then insert the user. To lock tables until the transaction completes, I use isolation level serializable . Here is the code:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- Range locks until transaction completes BEGIN TRAN DECLARE @maxUsers INT DECLARE @maxLabs INT DECLARE @userName VARCHAR(50) DECLARE @labZone INT DECLARE @labID INT SET @maxUsers = 4 SET @maxLabs = 5 SET @userName = 'Paul' SET @labZone = 51 SET @labID = NULL --Check potential spots ;WITH U1(LabID, UserName) AS( SELECT LabID, LabUserName FROM dbo.LabUsers WHERE LabID IN ( SELECT LabID FROM dbo.LabUsers WHERE LabUserName <> @userName GROUP BY LabID HAVING COUNT(LabUserName) < @maxUsers ) ) , U2(LabID, UserName) AS( SELECT LabID, LabUserName FROM dbo.LabUsers WHERE LabID IN ( SELECT LabID FROM dbo.LabUsers GROUP BY LabID HAVING COUNT(LabUserName) = @maxUsers ) ) --Get the first potential LabID SELECT @labID = ( SELECT TOP 1 potential.LabID FROM ( SELECT DISTINCT LabID, SUBSTRING((SELECT ',' + UserName FROM ( SELECT LabID, UserName FROM U1 UNION SELECT LabID, @userName FROM U1 ) t WHERE LabID = lu.LabID ORDER BY UserName FOR XML PATH('') ), 2, 50) AS AfterUsers, SUBSTRING((SELECT ',|' + UserName + '|' FROM ( SELECT LabID, UserName FROM U1 ) t WHERE LabID = lu.LabID ORDER BY UserName FOR XML PATH('') ), 2, 50) AS BeforeUsers FROM U1 lu) potential LEFT OUTER JOIN ( SELECT DISTINCT LabID, SUBSTRING((SELECT ',' + UserName FROM ( SELECT LabID, UserName FROM U2 UNION SELECT LabID, @userName FROM U2 ) t WHERE LabID = lu.LabID ORDER BY UserName FOR XML PATH('') ), 2, 50) AS Users FROM U2 lu) allocated ON potential.AfterUsers = allocated.Users WHERE allocated.Users IS NULL AND potential.BeforeUsers NOT LIKE '%|' + @userName + '|%' ORDER BY 1 ) IF @labID IS NULL --No existing lab available BEGIN --Insert Lab INSERT INTO dbo.Labs(LabName, LabZone) SELECT 'New Lab', @labZone WHERE (SELECT COUNT(*) FROM dbo.Labs) < @maxLabs IF @@ROWCOUNT = 1 BEGIN SET @labID = SCOPE_IDENTITY() --Get the new LabID --Insert Lab user INSERT INTO dbo.LabUsers(LabUserName, LabID) SELECT @userName, @labID END END ELSE --Lab exists, insert user if possible BEGIN INSERT INTO dbo.LabUsers(LabUserName, LabID) SELECT @userName, @labID WHERE NOT EXISTS(SELECT * FROM dbo.LabUsers WHERE LabID = @labID AND LabUserName = @userName) END --A quick select to check the results SELECT * FROM dbo.Labs SELECT DISTINCT LabID, SUBSTRING((SELECT ',' + LabUserName FROM ( SELECT LabID, LabUserName FROM dbo.LabUsers ) t WHERE LabID = lu.LabID ORDER BY LabUserName FOR XML PATH('') ), 2, 50) AS Users FROM dbo.LabUsers lu COMMIT TRAN SET TRANSACTION ISOLATION LEVEL READ COMMITTED --Restore isolation level to default 
0
source

Here's an attempt to solve this with MERGE . As part of this work, this solution creates ordered CSV lists and compares them, and therefore may not be very effective. However, in my tests, it seems to have fulfilled all other requirements.

First, a schema populated with an example from the original post:

 CREATE TABLE Labs (LabID int IDENTITY, LabName varchar(50), LabZone int); SET IDENTITY_INSERT Labs ON; INSERT INTO Labs (LabID, LabName, LabZone) VALUES (1, 'North' , 51), (2, 'North East', 51), (3, 'South West', 51); SET IDENTITY_INSERT Labs OFF; CREATE TABLE LabUsers (LabUserID int IDENTITY, LabUserName varchar(50), LabID int); SET IDENTITY_INSERT LabUsers ON; INSERT INTO LabUsers (LabUserID, LabUserName, LabID) VALUES ( 1, 'Diana', 3), ( 2, 'Julia', 2), ( 3, 'Paula', 2), ( 4, 'Romeo', 1), ( 5, 'Julia', 3), ( 6, 'Rose' , 2), ( 7, 'Diana', 1), ( 8, 'Diana', 2), ( 9, 'Julia', 1), (10, 'Romeo', 3), (11, 'Paul' , 1); SET IDENTITY_INSERT LabUsers OFF; 

script commented out, with parameters pre-initialized with some values:

 /* script parameters */ DECLARE @zone int = 51; DECLARE @maxLabs int = 3; DECLARE @maxUsers int = 4; DECLARE @userName varchar(50) = 'Paul'; /* auxiliary variables */ DECLARE @defLabName varchar(50) = 'New Lab'; DECLARE @SelectedLab table (LabID int); /* the main part begins */ WITH ZoneLabs AS ( /* get labs for the specified @zone */ SELECT LabID FROM Labs WHERE LabZone = @zone ) , IncompleteLabs AS ( /* get labs with the number of users < @maxUsers */ SELECT LabID FROM LabUsers WHERE LabID IN (SELECT LabID FROM ZoneLabs) GROUP BY LabID HAVING COUNT(*) < @maxUsers UNION ALL /* …and add a new lab if the number of labs < @maxLabs */ SELECT 0 FROM ZoneLabs HAVING COUNT(*) < @maxLabs ) , LabUsersAdjusted AS ( /* get all existing users */ SELECT LabUserID, LabUserName, LabID, 0 AS IsNew FROM LabUsers WHERE LabID IN (SELECT LabID FROM ZoneLabs) UNION ALL /* …and add the new user as a member of every incomplete lab unless the user is already a member */ SELECT 0 , @userName , LabID, 1 FROM IncompleteLabs WHERE LabID NOT IN (SELECT LabID FROM LabUsers WHERE LabUserName = @userName) ) , UsersGrouped AS ( /* get labs along with their CSV-lists of users */ SELECT LabID, OldUserCount = COUNT(NULLIF(IsNew, 1)), NewUserCount = SUM(IsNew), LabUsers = SUBSTRING( ( SELECT ',' + LabUserName FROM LabUsersAdjusted WHERE LabID = lu.LabID ORDER BY LabUserName FOR XML PATH('') ), 2, 2147483647 ) FROM LabUsersAdjusted lu GROUP BY LabID ) , SelectedLab AS ( /* (the crucial part) get one of the (currently) incomplete labs where the new user is being added: - exclude every lab whose set of users is going to match that of any existing full lab; - prioritise remaining labs by: 1) the number of users: more users = higher priority; 2) the order of addition: older labs (those with lower IDs) = higher priority; */ SELECT TOP 1 LabID FROM UsersGrouped new WHERE NewUserCount = 1 AND NOT EXISTS ( SELECT * FROM UsersGrouped old WHERE new.LabUsers = old.LabUsers AND old.OldUserCount = @maxUsers ) ORDER BY OldUserCount DESC, LabID ASC ) /* merge the selected lab into the existing lab set */ MERGE INTO Labs USING SelectedLab s ON (Labs.LabID = s.LabID) WHEN MATCHED THEN /* if there a match, just do nothing */ UPDATE SET @zone = @zone WHEN NOT MATCHED THEN /* when no match, add a new lab */ INSERT (LabName, LabZone) VALUES (@defLabName, @zone) /* in any event, remember the final LabID */ OUTPUT INSERTED.LabID INTO @SelectedLab (LabID) ; /* add the new user as a member of the stored LabID; if no LabID was OUTPUT by MERGE, then @SelectedLab contains no rows and, consequently, no user gets inserted */ INSERT INTO LabUsers (LabUserName, LabID) SELECT @userName, LabID FROM @SelectedLab ; /* return the remembered LabID or 0 */ SELECT ISNULL((SELECT LabID FROM @SelectedLab), 0) AS Result; 

In the above example and given parameter values, the script returns 0 . Play with arguments and / or pre-inserted data to see other results.

0
source

Source: https://habr.com/ru/post/1413654/


All Articles