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
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.