-- 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_selectvalues
                 @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 n FROM (VALUES(' +
               replace(replace(@list, @q1, @q2), @delimiter, N' ), (') +
               ')) AS a(n) ' +
              ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go

CREATE PROCEDURE unpackstr_with_selectvalues
                 @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 str FROM (VALUES(ltrim(rtrim(''' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       N'''))), (ltrim(rtrim(''') +
               ''')))) AS a(str)' + ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go

CREATE PROCEDURE unpack_with_insert2008
                 @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, '), (') +
               ')' + ' -- ' + convert(char(36), newid())
--PRINT @sql
EXEC (@sql)
go

CREATE PROCEDURE unpackstr_with_insert2008
                 @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,
                       '''))), (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