Created
April 6, 2018 09:19
-
-
Save KalinovDmitri/627a43d7bd5d59c29d0fa3866ea10734 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- declare all required tables | |
CREATE TABLE [dbo].[Abonents] | |
( | |
[Id] BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED, | |
[FirstName] NVARCHAR(64) NOT NULL, | |
[LastName] NVARCHAR(64) NOT NULL, | |
[Phone1] VARCHAR(20) NULL, | |
[Phone2] VARCHAR(20) NULL, | |
[Phone3] VARCHAR(20) NULL | |
); | |
CREATE TABLE [dbo].[PhoneNumbers] | |
( | |
[Id] BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED, | |
[Number] VARCHAR(20) NOT NULL, | |
[IsAvailable] BIT NOT NULL DEFAULT(1) | |
); | |
CREATE TABLE [dbo].[AbonentPhones] | |
( | |
[AbonentId] BIGINT NOT NULL, | |
[PhoneId] BIGINT NOT NULL, | |
PRIMARY KEY NONCLUSTERED ([AbonentId] ASC, [PhoneId] ASC) | |
); | |
-- collect information about existing phone numbers | |
DECLARE @AbonentPhonesInfo TABLE ([Id] BIGINT NOT NULL, [Number] VARCHAR(20) NOT NULL); | |
INSERT INTO @AbonentPhonesInfo ([Id], [Number]) | |
SELECT [AP].[Id], [AP].[Number] | |
FROM | |
( | |
SELECT [Id], [Phone1] AS [Number] | |
FROM [dbo].[Abonents] | |
UNION ALL | |
SELECT [Id], [Phone2] AS [Number] | |
FROM [dbo].[Abonents] | |
UNION ALL | |
SELECT [Id], [Phone3] AS [Number] | |
FROM [dbo].[Abonents] | |
) AS [AP] | |
WHERE [AP].[Number] IS NOT NULL; | |
-- fill [AbonentPhones] table | |
INSERT INTO [dbo].[AbonentPhones] ([AbonentId], [PhoneId]) | |
SELECT [AP].[Id], [P].[Id] | |
FROM [dbo].[PhoneNumbers] AS [P] | |
INNER JOIN @AbonentPhonesInfo AS [AP] ON ([AP].[Number] = [P].[Number]); | |
-- update [PhoneNumbers] table to set [IsAvailable] flag to 0 (false) | |
UPDATE [dbo].[PhoneNumbers] WITH(ROWLOCK) | |
SET [IsAvailable] = 0 | |
WHERE [Id] IN | |
( | |
SELECT [PhoneId] FROM [dbo].[AbonentPhones] | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment