AbonentPhones.sql

-- 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]
);