How to insert 1000 rows at a time

I create a student table with three column id, student,age. show you this example

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    insert into student values(@id, 'jack' + convert(varchar(5), @id), 12)
    select @id = @id + 1
end

this is the result about the example enter image description here


If you have a DataTable in your application, and this is where the 1000 names are coming from, you can use a table-valued parameter for this.

First, a table type:

CREATE TYPE dbo.Names AS TABLE
(
  Name NVARCHAR(255),
  email VARCHAR(320),
  [password] VARBINARY(32) -- surely you are not storing this as a string!?
);

Then a procedure to use this:

CREATE PROCEDURE dbo.Names_BulkInsert
  @Names dbo.Names READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.RealTable(Name, email, password)
    SELECT Name, email, password
    FROM @Names;
END
GO

Then your C# code can say:

SqlCommand cmd = new SqlCommand("dbo.Names_BulkInsert", connection_object);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter names = cmd.Parameters.AddWithValue("@Names", DataTableName);
names.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();

If you just want to generate 1000 rows with random values:

;WITH x AS
(
  SELECT TOP (1000) n = REPLACE(LEFT(name,32),'_','')
  FROM sys.all_columns ORDER BY NEWID()
)
-- INSERT dbo.sometable(name, email, [password])
SELECT 
  name = LEFT(n,3),
  email = RIGHT(n,5) + '@' + LEFT(n,2) + '.com', 
  [password] = CONVERT(VARBINARY(32), SUBSTRING(n, 1, 32))  
FROM x;

In neither of these cases should you be using while loops or cursors. IMHO.