Insert same record many times

Question

I have a case where I need to have an identical single record inserted many times (could be millions of times) into the database. (don't ask, long story that was a previous design decision by someone else that I can't change)

I'm trying to do this REALLY fast instead of 1 million inserts. Obviously I've seen union all and comma separated lists in SQL Server 2008, but this doesn't really solve the issue because there would still need to be MANY inserts.

I'm looking for a non-sql server specific syntax that would allow me to do this.  

Anyone have any suggestions?

Thanks!

Answer 1

This will insert 50 rows in table

create table #t1(col1 varchar(10))

insert into #t1
values('test')
go 50

select * from #t1

Answer 2

Obviously I've seen union all and comma separated lists in SQL Server 2008, but this doesn't really solve the issue because there would still need to be MANY inserts.

Not true, quite  the opposite. Doing it this is way is exactly to avoid multiple insertsd and have a single insert instead. However, doing one huge INSERT which is a fully logged operation regardless of recovery model can blow your transaction log, so it's better to batch it up into a few smaller inserts and commit in batches.

Have you thought of using SSIS for this? You could have a single row as input, write a simple script task trnasform that generates a million copies and use an oledb destination that commits in batches of 100,000 rows (which uses BULK insert under the hood and is very fast).

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 3

This will insert 50 rows in table

create table #t1(col1 varchar(10))



insert into #t1

values('test')

go 50



select * from #t1

GO 50 will execute the batch 50 times which means 50 separate INSERTs

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 4

Thanks!

It appears that I can't do this on a single ADO.NET command. Any ideas how I can issue the go in a command in ADO.NET?

Answer 5

Adam, my issue is that I'm in ADO.NET, this isn't something that is being run in management studio or something, so I have the limitations of what I can do from there.

I looked at using the Bulk Insert command, however I'd have to write out the file and then import it with the Bulk Insert and I figure it would likely take just as long to write teh file and read it back in as it would to do X inserts.

To complicate matters there is a insert trigger that writes to another table... so...

Just looking for reasonable speed. It isn't often that someone would insert 1 million but it can happen so I'm hoping to create something that would be reasonably fast (< 5 minutes would be reasonably fast in my book)

Answer 6

Hello JohnGalt,

You can try this,

Declare

 

@i int

set

 

@i=1

while

 

(@i<=20)

begin

Insert

 

into #temp(id)

Select

 

id from #temp

Set

 

@i=@i+1

end

 

In 20 Iteration, you will be inserting 1048576.. I think so it would be fast and then it would be doing batch of inserting instead of one row at a time.
See in eaach iteration records are getting double for insertion.

Hope it will help.

Answer 7

Try using an auxiliary table of numbers.

declare @n int;

set @n = 10000;

insert into T(c1)
select A.c1
from (select 1 as c1) as A inner join dbo.Number as N on N.Number <= @n;

or

insert into T(c1)
select A.c1
from (select 1 as c1) as A cross join (select Number dbo.Number as N on N.Number <= @n) as B;


Why should I consider using an auxiliary numbers table?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html


AMB

Answer 8

Ugh.... a million+ rows in a table all just to do this? I get the idea, and thanks for the suggestion, but I don't see that working.

Answer 9

See Linchi's post on Aaron's blog (towards the end) - it uses a loop, but batches the inserts up so the transactions are done in big commits. Very quick

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/07/bad-habits-to-kick-using-a-loop-to-populate-a-table.aspx

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 10

JohnGalt,

In addition to the solutions presented above, here is one more. This solution inserting over a million rows, clocked 11 seconds on a DELL workstation. It applies a @tablevariable to hold the insert batch of 65536 rows.

-- T-SQL Populating Large Tables
USE tempdb
-- DBCC is for timing control only - REMOVE FOR PRODUCTION
DBCC DROPCLEANBUFFERS 
DECLARE @Prototype TABLE ( ProductName varchar(50), 
          ProductNo varchar(20), Color varchar(12), ListPrice smallmoney)
-- Single row to be inserted
INSERT @Prototype
SELECT Name, ProductNumber, Color, ListPrice
FROM AdventureWorks2008.Production.Product
WHERE ProductID=800
SELECT * FROM @Prototype
/*
ProductName
Road-550-W Yellow, 44
*/
DECLARE @i smallint = 1
WHILE (@i <= 16)
BEGIN
	INSERT @Prototype SELECT * FROM @Prototype
	SET  @i += 1
END
SELECT COUNT(*) FROM @Prototype
-- 65536

-- The large table
SELECT ID=ProductID, Name, ProductNumber, Color, ListPrice
INTO HugeProduct
FROM AdventureWorks2008.Production.Product
--(504 row(s) affected)

SET @i = 1
WHILE ( @i < = 17)
BEGIN
	INSERT HugeProduct (Name, ProductNumber, Color, ListPrice)
	SELECT * FROM @Prototype
	SET @i += 1
END
SELECT COUNT(*) FROM HugeProduct
-- 1,114,616

SELECT TOP (7) * FROM HugeProduct ORDER BY NEWID();
/*
ID		Name				ProductNumber	Color	ListPrice
329996	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
695131	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
540355	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
586629	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
88577	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
996120	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
79631	Road-550-W Yellow, 44	BK-R64Y-44	Yellow	1120.49
*/

-- PERFORMANCE: 11 seconds

GO
DROP TABLE tempdb.dbo.HugeProduct

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 11

Hi Kalman,

Just out of interest, how does this behave from a transaction log and disk IO perspective? My understanding is that table variables are in-memory and that SQL server physicalises them in tempdb under certain circumstances? Hence wouldn't the multiple INSERTs in a loop incur a similar overhead?

Could you compare your suggestion with using a temp table or doing the N inserts directly to the table on your system to see the impact of using a table variable? 11 seconds sounds ok (especially for a 1 million row insert) but it would be better set in context compared to the alternatives.

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 12

Hi Adam,

The temp table version performed equally well, 11 sec. Since it is a small temp table apperantly the insert logging (64K) did not make significant difference.  For transaction logging, 1 million insert log entries on a narrow table. Below is the statistics io output ( temporary table version):

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(4 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(16 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(32 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(64 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 263, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(128 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 525, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(256 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 520, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(512 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 1039, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 2097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1024 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 4194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2048 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 8386, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(4096 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 16776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8192 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 33550, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 119, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(16384 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 67098, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(32768 row(s) affected)

(1 row(s) affected)
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(504 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)
Table 'HugeProduct'. Scan count 0, logical reads 66376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Prototype__________________________________________________________________________________________________________00000000007A'. Scan count 1, logical reads 473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(65536 row(s) affected)

(1 row(s) affected)
Table 'HugeProduct'. Scan count 3, logical reads 14289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(7 row(s) affected)
Table 'HugeProduct'. Scan count 3, logical reads 14289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 13

OP Said:
I'm looking for a non-sql server specific syntax that would allow me to do this.  

This statement puzzles me. Can you explain what you mean? Both SqlAge and now Kalman have provided a workable solution. Wrap their looping code intoa stored proc, pass in the columns for your row as parameters tot he stored proc and either use a table variable, a temp table or a numbers table to generate lots of copies of the row you need. Commit in batches instead of 1 row at a time and you have a solution.

Key point, do it in the relational layer rather than in your application layer (.NET code with ADO.NET). Data processing is what databases are designed for so let the RDBMS do it's job.

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 14

Thanks Kalman. so if using a temp table performs just as well, I wonder how these methods would compare to the auxillary numbers (table, cte, whatever) on your system?

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 15

Adam - Which one specifically?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 16

Let's say a numbers table which having been pre-populated should yield the best performance. Using the numbers table approach, you'd want to compare the following:
- 1 x 1,000,000 rows insert (single commit)
- 10 x 100,000 rows inserts (10 commits)
- 1000 x 1000 rows inserts (1000 commits)
- 1,000,000 x 1 row inserts (1,000,000 commits) -- base case the OP is trying to address hence no need for numbers table for thsi one

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 17

Adam, comparison results follows:

/* Populating a table of million rows

Single commit      4 sec
10 commits         7 sec
1000 commits       66 sec
*/
/********* SINGLE commit **********/
CREATE TABLE Million ( SeqNo int, Filler varchar(256), ModifiedDate datetime default (getdate()))
GO
DBCC DROPCLEANBUFFERS
SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 1000000;
WITH   n5   (x) AS (SELECT 1 UNION SELECT 0),   
n4   (x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),   
n3   (x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),   
n2   (x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),   
n1   (x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x),   
n0   (x) AS (SELECT 1 FROM n1 CROSS JOIN n1 AS x),   
Nbrs (x) AS    (   SELECT   ROW_NUMBER() OVER   (ORDER BY x)       
FROM n0   )
INSERT INTO Million(SeqNo, Filler)
SELECT [Number] = x, Filler=CONVERT(varchar(256),'SQL Server 2008')  
FROM Nbrs  WHERE x BETWEEN 1 AND @UpperLimit;
GO
-- 4 sec
DROP TABLE Million
GO
/******* 10 commits *************/
CREATE TABLE Million ( SeqNo int, Filler varchar(256), ModifiedDate datetime default (getdate()))
GO
DBCC DROPCLEANBUFFERS
SET NOCOUNT ON;
DECLARE @Batch INT = 100000, @i int = 0;
;WITH   n5   (x) AS (SELECT 1 UNION SELECT 0),   
n4   (x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),   
n3   (x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),   
n2   (x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),   
n1   (x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x),   
n0   (x) AS (SELECT 1 FROM n1 CROSS JOIN n1 AS x),   
Nbrs (x) AS    (   SELECT   ROW_NUMBER() OVER   (ORDER BY x)       
FROM n0   ) 
SELECT X into #1M
FROM Nbrs WHERE X between 1 and 1000000

WHILE (@i < 1000000/@Batch)
BEGIN
INSERT INTO Million(SeqNo, Filler)
SELECT [Number] = x, Filler=CONVERT(varchar(256),'SQL Server 2008')  
FROM #1M  WHERE x BETWEEN @i * @Batch AND @i * @Batch+@Batch;
SET @i += 1
END -- WHILE
GO
-- 7 sec
DROP TABLE Million
DROP TABLE #1M
GO

/******* 1000 commits *************/
CREATE TABLE Million ( SeqNo int, Filler varchar(256), ModifiedDate datetime default (getdate()))
GO
DBCC DROPCLEANBUFFERS
SET NOCOUNT ON;
DECLARE @Batch INT = 1000, @i int = 0;
;WITH   n5   (x) AS (SELECT 1 UNION SELECT 0),   
n4   (x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),   
n3   (x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),   
n2   (x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),   
n1   (x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x),   
n0   (x) AS (SELECT 1 FROM n1 CROSS JOIN n1 AS x),   
Nbrs (x) AS    (   SELECT   ROW_NUMBER() OVER   (ORDER BY x)       
FROM n0   ) 
SELECT X into #1M
FROM Nbrs WHERE X between 1 and 1000000

WHILE (@i < 1000000/@Batch)
BEGIN
INSERT INTO Million(SeqNo, Filler)
SELECT [Number] = x, Filler=CONVERT(varchar(256),'SQL Server 2008')  
FROM #1M  WHERE x BETWEEN @i * @Batch AND @i * @Batch+@Batch;
SET @i += 1
END -- WHILE
GO
-- 67 sec
DROP TABLE Million
DROP TABLE #1M
GO

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 18

Excellent. Thanks for taking the time to do this. The single large commit appears to be the most performant in this case. I've seen situations where this can blow the transaction log or if the log file or tempdb is not large enough to start with then SQl server keeps requesting to grow the file which would slows this down considerably.

So, hopefully the OP now has enough information to make an informed decision.

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 19

Thanks so much for all of your suggestions everyone!  I eventually did a temp table of numbers and then inserted. Works pretty well!

The reason for trying to make this generic is because we have to support multiple database engines. Using a stored proc works because we can replicate it in other DBs in the future.

Answer 20

Great. Can you please mark the post(s) that helped you get your solution with "Mark as Answer".

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 21

Done! Thanks!