Inserting Dummy Records using loop statement

Question

Inserting Dummy Records using loop statement

darren-hong

SSC Enthusiast

Points: 135
More actions
July 17, 2015 at 6:49 am

#318992

Hi all,
I have the following attributes in this Table A.
1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)
I am trying to create 1000000 dummy records in this this table A
How do i go about do it? I would like my data to be something like this
LOCATION_ID
1
Serial Number
SN-01
KeyID
E1210
Reference_Address
83
SourceTime
2015-05-21 00:00:00 000
SourceValue
6200
Thank you, Hope to hear ur reply soon!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

can we assume that the dummy records are all to be different eg different dates/serial numbers etc but in the format you describe?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

These articles might help you:

Generating Test Data: Part 1 - Generating Random Integers and Floats[/url]

Generating Test Data: Part 2 - Generating Sequential and Random Dates[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

darren-hong (7/17/2015)
Hi all,
I have the following attributes in this Table A.
1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)
I am trying to create 1000000 dummy records in this this table A
How do i go about do it? I would like my data to be something like this
LOCATION_ID
1
Serial Number
SN-01
KeyID
E1210
Reference_Address
83
SourceTime
2015-05-21 00:00:00 000
SourceValue
6200
Thank you, Hope to hear ur reply soon!

Quick thoughts, the data types seem somewhat strange, doubt you'll need nvarchar(max) for serial number, key id and reference address. Further on this, a single character nvarchar for source value does not look right either.

😎

darren-hong SSC Enthusiast Points: 135 More actions · Answer 4

darren-hong

SSC Enthusiast

Points: 135

July 17, 2015 at 9:58 am

#1813361

All data are different

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

as an idea

SELECT TOP 100

LocID = 1 + CAST(ABS(CHECKSUM(NEWID()) % 9) AS int),

SerialNo = 'SN-' + CAST(ABS(CHECKSUM(NEWID()) % 90000) AS varchar(5)),

KeyID = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) + CAST(ABS(CHECKSUM(NEWID()) % 9000) AS varchar(4)),

Ref_Add = CAST(ABS(CHECKSUM(NEWID()) % 90) AS varchar(2)),

SourceTime = DATEADD(dd , ABS(CHECKSUM(NEWID())) % DATEDIFF(dd , '2014' , '2015') , '2014'),

SourceValue = 1 + CAST(ABS(CHECKSUM(NEWID()) % 9000) AS int)

--INTO ATABLE

FROM sys.all_columns AS ac1

CROSS JOIN sys.all_columns AS ac2

CROSS JOIN sys.all_columns AS ac3;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

darren-hong SSC Enthusiast Points: 135 More actions · Answer 6

Hi Thanks for your help.

Could u include Insert statement as well ?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

darren-hong (7/17/2015)
Hi Thanks for your help.
Could u include Insert statement as well ?

uncomment this line

--INTO ATABLE

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day