How to insert 1000 rows at a time

Question

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

User · Accepted Answer

I create a student table with three column id  student age  show you this example  declare  id int  select  id   1 while  id  gt  1 and  id  lt   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

User · Answer

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

User · Answer

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

User · Answer

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  lt  1000    SELECT rowid sname semail spassword  FROM thetable ORDER BY rowid OPTION  MAXRECURSION 1000

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

DECLARE  X INT   1 WHILE  X  lt  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  lt  1000 BEGIN     INSERT INTO dbo YourTable  ID  Age      VALUES  X LEFT RAND   100 2       SET  X  1 END

User · Answer

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

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

Examples related to sql-server

Examples related to database