Insert n number of rows

Question

I want to insert dummy data to my table let's say 10000 records using loop or whatever. Table definition as follows:

ID (PK & AI) | ArticelNumber (varchar unique) | CreateDate (Datetime)

What criteria? Or you ask how to insert 10000 default values? — sepupic, Sep 29 '17 at 12:29
i am not very good in sql, i ment to insert x number of rows — Tony, Sep 29 '17 at 12:37
create a client function that populate that database with random generated values. put that in a loop from 0 to 10000 — Damien, Sep 29 '17 at 12:47
Possible duplicate of [Quickest way to fill SQL Table with Dummy Data](https://stackoverflow.com/questions/2899654/quickest-way-to-fill-sql-table-with-dummy-data) — Tanner, Sep 29 '17 at 13:11
so many **[other questions](https://stackoverflow.com/search?q=%5Bsql-server%5D+create+dummy+data)** that could help you. — Tanner, Sep 29 '17 at 13:13

Pawel Czapski · Answer 1 · 2017-09-29T16:40:56.050

I think below will meet your criteria, ask in comments if any questions:

-- create table
create table #tmp 
  (ID int primary key, 
  ArticeNumber nvarchar(50), 
  CreateDate datetime)
go

-- loop with insert
declare @incr int = 1
while (@incr < 10001)
begin
    -- use "getdate() - @incr" as below if you want to have diffrent dates or just getdate() if not  
    insert into #tmp
    select @incr, newid(), getdate() - @incr

    -- second option to insert below
    -- insert into #tmp
    -- select @incr, 'ArticleNo: ' + cast(@incr as nvarchar), getdate() - @incr

    -- increment int
    set @incr = @incr + 1
end
go

-- select your date
select * from #tmp

-- below is just to prove ArticleNumbert is unique 
select distinct ArticeNumber from #tmp

-- drop table if necessary
drop table #tmp

got an error: An explicit value for the identity column in table 'dbo.T_Artikel' can only be specified when a column list is used and IDENTITY_INSERT is ON. — Tony, Sep 29 '17 at 16:10

sepupic · Answer 2 · 2017-09-29T19:36:14.550

0

Ok, if your table has id identity, and all you need is to insert unique ArticleNumber and current date you can use top 10000 from any table containing at least 10000 rows, or this way:

insert into yourTble(ArticleNumber, CreateDate)
select top 10000 newid(), getdate()
from sys.all_columns cross join sys.all_columns c1;

edited Sep 29 '17 at 19:36

answered Sep 29 '17 at 12:41

sepupic

8,409
1
9
20

score 0 · Answer 3 · answered Sep 29 '17 at 12:53

0

Or just use GO 10000 e.g.

INSERT INTO <yourtable>(DatetimeCol, varcharCol, IntegerCol,...)
VALUES(sysdatetime(),'<yourtext>',<yourInt>,...);

GO 10000

answered Sep 29 '17 at 12:53

SQLBadPanda

625
5
7

score 0 · Answer 4 · answered Sep 29 '17 at 13:19

0

You can use http://generatedata.com/ website

It's easy to understand how it works,and you can put the criteria you want

answered Sep 29 '17 at 13:19

Hakan Hifzioglu

35
5

But **what** do you want to insert? random data? sequential data? Is the ID an identity column and what datatype is it? do you different data in each row, the same data, different data but periodically repeated (i.e randomized from a set)? Does it need to relate to anything else? I could go on, as I said below the simplest is to write an Insert statement and repeat it 10000 times (assuming the ID is an identity or has a default) – SQLBadPanda Sep 29 '17 at 15:06
@JurgenHakanHifzi just whatever - random data i am tying to make a loop but have no idea how to generate for each column "ArticelNumber" diffrent value as this col is unique. id col is just integer. – Tony Sep 29 '17 at 15:53

score -1 · Answer 5 · answered Sep 29 '17 at 12:33

-1

You can insert default values with that insert query;

INSERT INTO table DEFAULT VALUES

answered Sep 29 '17 at 12:33

Abdulkadir Erkmen

202
1
4

Insert n number of rows

5 Answers5