SET NOCOUNT ON;
USE tempdb;
GO
-- create table
CREATE TABLE TestInserts
(
Id INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
,SomeInt INT
,SomeText VARCHAR(20)
);
GO
-- variables for INSERT statements
DECLARE
@sqlInserts NVARCHAR(MAX)
,@sqlRows NVARCHAR(MAX)
,@sqlUnion NVARCHAR(MAX)
;
-- create INSERT statements
WITH cte (Num) AS
(
SELECT TOP(1000)
CONVERT(VARCHAR(20), Number)
FROM master.dbo.spt_values WHERE type = 'P'
)
SELECT
-- single inserts
@sqlInserts = (
SELECT 'INSERT INTO TestInserts VALUES (' + Num + ', ''Text: ' + Num + ''');' + CHAR(10)
FROM cte
FOR XML PATH('')
)
-- using 2008 row constructor notation
,@sqlRows =
'INSERT INTO TestInserts VALUES ' + CHAR(10)
+ STUFF(
(
SELECT ',(' + Num + ', ''Text: ' + Num + ''')' + CHAR(10)
FROM cte
FOR XML PATH('')
)
,1,1,N'')
-- using UNION ALL notation
,@sqlUnion =
'INSERT INTO TestInserts ' + CHAR(10)
+ STUFF(
(
SELECT 'UNION ALL SELECT ' + Num + ', ''Text: ' + Num + '''' + CHAR(10)
FROM cte
FOR XML PATH('')
)
,1,10,N'')
DECLARE
@now DATETIME2
,@offset INT;
-- exec single inserts
SELECT @now = SYSDATETIME();
EXECUTE sp_executesql @sqlInserts;
SELECT @offset = DATEDIFF(MILLISECOND, @now, SYSDATETIME());
PRINT ('Single Inserts: ' + CONVERT(VARCHAR(10), @offset) + 'ms');
-- exec row constructors
SELECT @now = SYSDATETIME();
EXECUTE sp_executesql @sqlRows;
SELECT @offset = DATEDIFF(MILLISECOND, @now, SYSDATETIME());
PRINT ('Row Constructor: ' + CONVERT(VARCHAR(10), @offset) + 'ms');
-- exec UNION ALL
SELECT @now = SYSDATETIME();
EXECUTE sp_executesql @sqlUnion;
SELECT @offset = DATEDIFF(MILLISECOND, @now, SYSDATETIME());
PRINT ('Union All: ' + CONVERT(VARCHAR(10), @offset) + 'ms');