-- In all procedures there is an addition of a comment with a GUID in it.
-- Without it the UNPACK operation that runs with the same input as the JOIN
-- operation would get a cache hit that would distort the test results.

CREATE PROCEDURE unpack_with_union
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = N',' AS
DECLARE @sql nvarchar(MAX),
        @q1  char(1),
        @q2  char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT INTO ' + @tbl + ' SELECT ' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       N' UNION ALL SELECT ') +
              ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go



CREATE PROCEDURE unpackstr_with_union
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = N',' AS
DECLARE @sql nvarchar(MAX),
        @q1     char(1),
        @q2     char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT INTO ' + @tbl + ' SELECT ltrim(rtrim(''' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       N''')) UNION ALL SELECT ltrim(rtrim(''') +
               '''))' + ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go



CREATE PROCEDURE unpack_with_insert
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = N',' AS
DECLARE @sql nvarchar(MAX),
        @q1     char(1),
        @q2     char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT ' + @tbl + ' VALUES (' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       ') INSERT ' + @tbl + ' VALUES (') + ')'
              + ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go



CREATE PROCEDURE unpackstr_with_insert
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = N',' AS
DECLARE @sql nvarchar(MAX),
        @q1     char(1),
        @q2     char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT ' + @tbl + ' VALUES (ltrim(rtrim(''' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       '''))) INSERT ' + @tbl + ' VALUES (ltrim(rtrim(''') +
               ''')))' + ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)



go
CREATE PROCEDURE unpack_with_manyselect
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = ',' AS
DECLARE @sql nvarchar(MAX),
        @q1     char(1),
        @q2     char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT ' + @tbl + ' EXEC(' + @q1 + 'SELECT ' +
               replace(replace(@list, @q1 COLLATE Slovenian_BIN2, @q2 + @q2),
                       @delimiter COLLATE Slovenian_BIN2,
                       ' SELECT ') + ' -- ' + convert(char(36), newid()) + @q1 + ')'
--PRINT @sql
EXEC (@sql)


go
CREATE PROCEDURE unpackstr_with_manyselect
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = ',' AS
DECLARE @sql    nvarchar(MAX),
        @q1     char(1),
        @q2     char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT ' + @tbl + ' EXEC(' + @q1 + 'SELECT ltrim(rtrim(' + @q2 +
               replace(replace(@list, @q1 COLLATE Slovenian_BIN2, @q2 + @q2),
                       @delimiter COLLATE Slovenian_BIN2,
                       @q2 + ')) SELECT ltrim(rtrim(' + @q2) +
               @q2 + '))' + ' -- ' + convert(char(36), newid()) + @q1 + ')'
--PRINT @sql
EXEC (@sql)
go