CODEWITHSUNDEEP
sql-serverdatabase
Zoya Sheikh
Zoya Sheikh

Reputation: 939

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?

Upvotes: 38

Views: 188604

Answers (11)

Mehdi Hassani Goodarzi
Mehdi Hassani Goodarzi

Reputation: 157

You can use GO command for iterated above commands

INSERT INTO db(@names,@email,@password) 
VALUES('abc','def','mypassword')

GO 1000;

when you have identity column: for example:

DROP TABLE student
CREATE TABLE student (Id INT IDENTITY(1,1),FirstName varchar(10),PhoneNumber int)

---- after create table select this section and execute

INSERT INTO student VALUES( 'jack' + convert(VARCHAR(5), ISNULL(SCOPE_IDENTITY(),0)+1), 12)
GO 1000

Upvotes: 0

roudlek
roudlek

Reputation: 385

if you're a beginner to sql like me us this

 create procedure insert1000record as
DECLARE @nbr INT=0
WHILE @nbr <1000
BEGIN
 insert into clients(client_ID) values(@nbr)
 SET @nbr = @nbr + 1
END

replace clients with your table name, and client_ID with your column_name when you finish execute the procedure with:

exec insert1000record

Upvotes: -1

De Wet Ellis
De Wet Ellis

Reputation: 750

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);

Upvotes: 1

Hemanshu Trivedi
Hemanshu Trivedi

Reputation: 1

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')

Upvotes: 0

Simon
Simon

Reputation: 43

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;

Upvotes: 0

DareDevil
DareDevil

Reputation: 5349

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

Upvotes: 2

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

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

Upvotes: 3

very9527
very9527

Reputation: 959

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

Upvotes: 66

anon
anon

Reputation:

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.

Upvotes: 14

to StackOverflow
to StackOverflow

Reputation: 124696

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.

Upvotes: 4

Guffa
Guffa

Reputation: 700342

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.

Upvotes: 3

Related Questions