[sql-server] How to insert 1000 rows at a time

I made Wpf appliaction. I want to test it with 1000 values in grid. I want to check that whether my grid will load 1000 data records fastly or not. So how to write one query to insert more than 1000 records in my database table. can i use for loop.

Insert into db(@names,@email,@password) Values('abc','def','mypassword');

I am using Sql-Server 2012 and ADO.net Connectivity! I want to execute this query in database to generate 1000 rows

EDIT

What if i want to insert unique names?

You can of course use a loop, or you can insert them in a single statement, e.g.

Insert into db
(names,email,password) 
Values
('abc','def','mypassword')
,('ghi','jkl','mypassword2')
,('mno','pqr','mypassword3')

It really depends where you're getting your data from.

If you use a loop, wrapping it in a transaction will make it a bit faster.

UPDATE

What if i want to insert unique names?

If you want to insert unique names, then you need to generate data with unique names. One way to do this is to use Visual Studio to generate test data.

You can insert multiple records by inserting from a result:

insert into db (@names,@email,@password) 
select 'abc','def','mypassword' union all
select 'abc','def','mypassword' union all
select 'abc','def','mypassword' union all
select 'abc','def','mypassword' union all
select 'abc','def','mypassword' union all
select 'abc','def','mypassword'

Just add as many records you like. There may be limitations on the complexity of the query though, so it might not be possible to add as many as 1000 records at once.

You can use the following CTE as well. You can just modify it as you find fit. But this will add the same values into the student CTE.

This will add 1000 records but you can change it to 10000 or to a maximum of 32767

;WITH thetable(rowid,sname,semail,spassword) AS
(
    SELECT 1  , 'name' , 'email' , 'password'
    UNION ALL
    SELECT rowid+1 ,'name' , 'email' , 'password' 
    FROM thetable WHERE rowid < 1000
)

SELECT rowid,sname,semail,spassword 
FROM thetable ORDER BY rowid
OPTION (MAXRECURSION 1000);

Using a @Aaron Bertrand idea (FROM sys.all_columns), this is something that will create 1000 records :

 SELECT TOP (1000) LEFT(name,20) as names,
                   RIGHT(name,12) + '@' + LEFT(name,12) + '.com' as email, 
                   sys.fn_sqlvarbasetostr(HASHBYTES('MD5', name)) as password
 INTO db
 FROM sys.all_columns

See SQLFIDDLE

DECLARE @X INT = 1
WHILE @X <=1000
BEGIN
    INSERT INTO dbo.YourTable (ID, Age)
    VALUES(@X,LEFT(RAND()*100,2) 
    SET @X+=1
END;

    enter code here
DECLARE @X INT = 1
WHILE @X <=1000
BEGIN
    INSERT INTO dbo.YourTable (ID, Age)
    VALUES(@X,LEFT(RAND()*100,2) 
    SET @X+=1
END;

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.

Simplest way.

Just stop execution in 10 sec.

Create table SQL_test  ( ID INT IDENTITY(1,1), UserName varchar(100)) 
while 1=1 
insert into SQL_test values ('TEST') 

By the way why don't you use XML data insertion through Stored Procedure?

Here is the link to do that... Inserting Bulk Data through XML-Stored Procedure