Perform a Bulk Insert and Decrypt Symmetric Key in SQL Server 2014

Question

I have a stored procedure that performs a Symmetric Key decrypt then an insert into a table if the decrypted value matches a value in another table.

I would like the procedure to be modified to perform a bulk insert, instead of opening and closing the DB connection on each row in order to perform the procedure.

My thought was to perform a decrypt on the entire column (EmployeeSeed.SEED) then a join on Employee_FN inside the insert statement.

Does anyone have any recommendations as to how to convert this to a bulk insert?

Here is my code:

USE [Tracker]
GO

/****** Object:  StoredProcedure [dbo].[VACATION]   Script Date: 7/20/2017 3:10:18 PM ******/
SET ANSI_NULLS ON -- return no rows if null value is present
GO
SET QUOTED_IDENTIFIER ON -- define literals to be delimited by '' and identifiers by ""
GO

ALTER PROCEDURE [dbo].[VACATION]
    @ID nvarchar(40),
    @HOURS float,
    @DESC nvarchar(max),
    @TYPE nvarchar(50)
AS

OPEN SYMMETRIC KEY EmployeeCert_Key
      DECRYPTION BY CERTIFICATE EmployeeCert

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @seed VARBINARY(16)
    DECLARE @EMPLOYEE_FN nvarchar(20)


--Select Employee FN based on Decrypted ID
SET @EMPLOYEE_FN =
(

SELECT EMPLOYEE.[EMPLOYEE_FN]

FROM Employee
JOIN EmployeeSeed
  ON Employee.EMPLOYEE_FN = EMPLOYEESEED.EMPLOYEE_FN
WHERE (CONVERT(nvarchar,                        -- Convert from binary to NVARCHAR
       DECRYPTBYKEY(ID, 1,             -- Designate column (1 is same as encrypted)
         HASHBYTES('SHA1', EmployeeSeed.SEED))) -- SHA1 seed
         = @ID)
)

--insert into table
IF @EMPLOYEE_FN is not null
BEGIN
    INSERT INTO [dbo].VACATION_BENEFIT
            ([EMPLOYEE_FN]
            ,[HOURS]
            ,[TYPE]
            ,[DESC])
            VALUES
               (@EMPLOYEE_FN,
                @HOURS,
                @TYPE,
                @DESC)

END

CLOSE SYMMETRIC KEY EmployeeCert_Key
END

Answer 1

you can create user defined type table to pass bulk data.

   CREATE TYPE UT_VacationDetails AS TABLE  
    (  
    ID  nvarchar(40),  
    [HOURS] float, 
    [DESC] nvarchar(max),  
    [TYPE] varchar(50)  
    )  
    GO
    USE [Tracker]
    USE [Tracker]
    GO
     /****** Object:  StoredProcedure [dbo].[VACATION]   Script Date: 
     7/20/2017 3:10:18 PM ******/
    SET ANSI_NULLS ON -- return no rows if null value is present
    GO
    SET QUOTED_IDENTIFIER ON -- define literals to be delimited by '' and 
    identifiers by ""
    GO


    ALTER PROCEDURE [dbo].[VACATION]
            -- Add the parameters for the stored procedure here
            @UT_details UT_VacationDetails readonly
    AS

    OPEN SYMMETRIC KEY EmployeeCert_Key
    DECRYPTION BY CERTIFICATE EmployeeCert

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
     SET NOCOUNT ON;

    DECLARE @seed VARBINARY(16)
    DECLARE @EMPLOYEE_FN nvarchar(20)

    INSERT INTO [dbo].VACATION_BENEFIT ([EMPLOYEE_FN]
        ,[HOURS]
        ,[TYPE]
        ,[DESC])
   SELECT EMPLOYEE.[EMPLOYEE_FN],T.[HOURS], T.[Type],T.[DESC]
   FROM Employee
   JOIN EmployeeSeed
   ON Employee.EMPLOYEE_FN = EMPLOYEESEED.EMPLOYEE_FN
   INNER JOIN @UT_details t On t.ID=  (CONVERT(nvarchar, DECRYPTBYKEY(ID, 1, 
   HASHBYTES('SHA1', EmployeeSeed.SEED)))



   CLOSE SYMMETRIC KEY EmployeeCert_Key
   END