SUBSCRIPTIONS_INSERT.sql

-------------------------------------------------------------------------------------------------
-- SUBSCRIPTIONS_INSERT
-------------------------------------------------------------------------------------------------
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @IS_COMMIT BIT = 0;
-------------------------------------------------------------------------------------------------
USE VideoHosting;

BEGIN TRAN
PRINT N'➕ JOB IS STARTED';

IF NOT EXISTS (SELECT 1 FROM [sys].[tables] WHERE [name] = N'SUBSCRIPTIONS') BEGIN
	PRINT N'❌ TABLE [SUBSCRIPTIONS] WAS NOT FOUND';
END ELSE BEGIN
    -- CREATE VARIABLES
    DECLARE @TO_USER NVARCHAR(32);
    DECLARE @FROM_USER NVARCHAR(32);

    DECLARE @TO_USER_UID UNIQUEIDENTIFIER;
    DECLARE @FROM_USER_UID UNIQUEIDENTIFIER;

    -- CREATE TMP TABLES
    BEGIN 
        DROP TABLE IF EXISTS #CSV;
        CREATE TABLE #CSV (
            [FROM_USER] NVARCHAR(32) NOT NULL,
            [TO_USER] NVARCHAR(32) NOT NULL,
        );
        BULK INSERT #CSV FROM '/DataCsv/subscriptions.csv'
        WITH (
	        FIRSTROW = 2,
            ROWTERMINATOR = '\n',
	        FIELDTERMINATOR = ';',
            DATAFILETYPE = 'Char',
            TABLOCK
        );
        PRINT N'➕ CREATE TEMP TABLES IS SUCCESS';
    END;

    -- INSERT TABLE
    BEGIN
        DECLARE CUR CURSOR FOR SELECT [FROM_USER], [TO_USER] FROM #CSV;
        OPEN CUR;
        FETCH NEXT FROM CUR INTO @FROM_USER, @TO_USER;
        WHILE @@FETCH_STATUS = 0 BEGIN
            SET @TO_USER_UID = (SELECT [UID] FROM [USERS] WHERE [USERNAME] = @TO_USER);
            SET @FROM_USER_UID = (SELECT [UID] FROM [USERS] WHERE [USERNAME] = @FROM_USER);
            IF NOT EXISTS (SELECT 1 FROM [SUBSCRIPTIONS] WHERE [FROM_USER_UID] = @FROM_USER_UID AND [TO_USER_UID] = @TO_USER_UID)
                INSERT INTO [SUBSCRIPTIONS] ([FROM_USER_UID], [TO_USER_UID]) VALUES (@FROM_USER_UID, @TO_USER_UID);
            FETCH NEXT FROM CUR INTO @FROM_USER, @TO_USER;
        END;
        CLOSE CUR;
        DEALLOCATE CUR;
        PRINT N'➕ INSERT IS SUCCESS';
    END;

    DROP TABLE IF EXISTS #CSV;
    PRINT N'➕ DROP TEMP TABLES IS SUCCESS';
END;


IF (@IS_COMMIT = 1) BEGIN
    COMMIT TRAN
    PRINT N'➕ INSERT IS COMMITTED';
END ELSE BEGIN
    ROLLBACK TRAN
    PRINT N'❌ INSERT IS ROLL-BACKED';
END;