CODEWITHSUNDEEP
sqlsql-servert-sql
wafw1971
wafw1971

Reputation: 361

Creating Random Data

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

Upvotes: 1

Views: 118

Answers (2)

HPS_PRS
HPS_PRS

Reputation: 1

Change statement to

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

Upvotes: 0

Devart
Devart

Reputation: 121922

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

Upvotes: 1

Related Questions