title: Fastest way to insert 100000 records into SQL Server
body: <p>I am using the following script to insert 100,000 records into a table. Basically int from 500,001 to 600,000 are inserted. I am casting the integer into a string and inserting coz thats how i want it in the table (an integer in the form of string). I am using a merge to check if the record already exists or not.</p>
  
  <pre><code>DECLARE @first AS INT
  SET @first = 500001
  DECLARE @step AS INT
  SET @step = 1
  DECLARE @last AS INT
  SET @last = 600000
  
  BEGIN TRANSACTION
  WHILE(@first &lt;= @last)
  BEGIN
  MERGE dbo.Identifiers As target
  USING (SELECT CAST(@first as varchar(10)) AS Identifier) AS source 
      ON (source.Identifier = target.Identifier)
  WHEN NOT MATCHED THEN
  INSERT (Identifier) 
  VALUES (source.Identifier);
  SET @first += @step
  END
  COMMIT TRANSACTION
  </code></pre>
  
  <p>Its taking more than 2 minutes to load. I am doing something terribly wrong but unable to trace out where.
  Note: The table has unique non-clustered index on Identifier Column.</p>