How SQL Server inserts data in bulk

Run the following script to build the test database and table.

--Create databasecreate database bulktestdb;gouse bulktestdb;go--int  primary KEY, UserName nvarchar (+), Pwd varchar (+)) Go--int, UserName nvarchar (+), Pwd varchar (+))

View Code

Use the simplest INSERT statement to iterate through the 1 million data.

Select COUNT(*) frombulktesttable;--Delete from bulktesttable;DECLARE @id INT,@username NVARCHAR( +),@pwd varchar( -)SET @id=1SET @username ='KK'SET @pwd='123' while @id<1000000   --10000 is the number of times you want to perform the insertionBEGININSERT  intoBulktesttable (Id, Username,pwd)--xx is the name of the tableVALUES  ( @id,@username,@pwd ) SET @id=@id+1END

View Code

---takes 3 minutes, 09 seconds.

How SQL Server inserts data in bulk