Insert billions of records

README.md

Insert billions of records

CREATE TABLE bigtable (
    column1 varchar(MAX),
    column2 varchar(MAX),
    column3 varchar(MAX),
 	column4 varchar(MAX),
 	column5 varchar(MAX),
 	column6 varchar(MAX),
 	column7 varchar(MAX),
 	column8 varchar(MAX),
 	column9 varchar(MAX),
 	column10 varchar(MAX),
 	column11 varchar(MAX),
    column12 varchar(MAX),
    column13 varchar(MAX),
 	column14 varchar(MAX),
 	column15 varchar(MAX),
 	column16 varchar(MAX),
 	column17 varchar(MAX),
 	column18 varchar(MAX),
 	column19 varchar(MAX),
 	column20 varchar(MAX),
); 

DECLARE @first AS INT = 1
DECLARE @last AS INT = 10001

WHILE(@first <= @last)
BEGIN
    INSERT INTO [dbo].[bigtable] VALUES('xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx','xxxxxxxxxxxxxxxxxxxx' )
    SET @first += 1
END

SET @first = 1

WHILE(@first <= @last)
BEGIN
    INSERT INTO [dbo].[bigtable]
	  SELECT TOP 1000
	    *
	  FROM [dbo].[bigtable]


    SET @first += 1
END

SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 / 1024.0 AS DbSizeInGB
FROM sys.dm_db_partition_stats

DBCC SQLPERF(logspace)
DBCC SHRINKDATABASE(maxDB)


SELECT name , physical_name, size/128 AS 'Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;