Creating Random Data

Question

I am trying to create some random data in my database, as you can see from the code below my Order number works fine and so does my order date but I cant get my OrderLineNumber to work, I want it to start 001 and run for 100 hundred rows and end on 100. I reckon its a simple answer but I just cant figure it out.

DECLARE @OrderNumber varchar (30)
DECLARE @OrderDate int
DECLARE @OrderLineNumber varchar(50)

SET @OrderNumber = 1
SET @OrderDate = 0
SET @OrderLineNumber = 001

WHILE @OrderNumber <= 100
WHILE @OrderDate <= 100
WHILE @OrderLineNumber <= 100

BEGIN
INSERT INTO Orders (OrderNumber, OrderDate, OrderLineNumber)
SELECT 'ORD' + Right ('000000' + CAST (@OrderNumber AS varchar (30)), 6),
DATEADD (day, CAST (RAND () * 1500 as int), '2008-1-1'),
(@OrderLineNumber = 0)

SET @OrderNumber = @OrderNumber + 1
SET @OrderDate = @OrderDate + 1
SET @OrderLineNumber = @OrderLineNumber +1
END

slightly related: check this out github.com/fzaninotto/Faker. related: what is the current result of that procedure? — jtavares
– jtavares, Commented May 22, 2013 at 11:58
Hey jtavares, the first two columns populate the third column doesn't. — wafw1971
– wafw1971, Commented May 22, 2013 at 12:01
I have now but I get the following error message - Msg 102, Level 15, State 1, Line 17 Incorrect syntax near '='. — wafw1971
– wafw1971, Commented May 22, 2013 at 12:08
and why is that = there? shouldnt it be just (@OrderLineNumber)? — jtavares
– jtavares, Commented May 22, 2013 at 12:15

Devart · Accepted Answer · 2013-05-22 12:17:44Z

1

Try this one -

DECLARE @OrderNumber VARCHAR(30)
DECLARE @OrderDate INT
DECLARE @OrderLineNumber VARCHAR(50)

SET @OrderNumber=1
SET @OrderDate=0
SET @OrderLineNumber=001

WHILE @OrderNumber<=100
    WHILE @OrderDate<=100
        WHILE @OrderLineNumber<=100

        BEGIN
            INSERT INTO dbo.Orders (OrderNumber, OrderDate, OrderLineNumber)
                SELECT  'ORD'+RIGHT('000000'+CAST(@OrderNumber AS VARCHAR(30)),6)
                    ,   DATEADD(DAY,CAST(RAND()*1500 AS INT),'2008-1-1')
                    ,   @OrderLineNumber --<--

            SELECT @OrderNumber=@OrderNumber+1
                 , @OrderDate=@OrderDate+1
                 , @OrderLineNumber=@OrderLineNumber+1
        END

edited May 22, 2013 at 12:17

answered May 22, 2013 at 12:11

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Prikryl · Accepted Answer · 2013-05-22 12:36:21Z

0

Change statement to

SELECT 'ORD' + Right ('000000' + CAST (@OrderNumber AS varchar (30)), 6),
DATEADD (day, CAST (RAND () * 1500 as int), '2008-1-1'),@OrderLineNumber

edited May 22, 2013 at 12:36

Martin Prikryl

205k64 gold badges560 silver badges1.1k bronze badges

answered May 22, 2013 at 12:16

HPS_PRS

1

Collectives™ on Stack Overflow

Creating Random Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related