Procedure to copy table using random function as lookup

Transact-SQL
1

Hi all,

I have the below table (only chosen 5 rows from 300,00 rows)

Row id Current Item Number lines units Cumulative %SKU %Lines First SKU Last SKU
1 1 14 39 217 217 58 50 1 60
2 1 6 5 8 225 60 54 61 120
3 1 7 5 5 230 73 59 121 190
4 1 1 1 2 232 75 63 191 290
5 1 3 1 1 233 80 73 291 333

Im able to copy the above table to a new table and copy and add n number of rows, ie growing the product range from 300,000 to 350,000.

But I dont want to randomly choose any row, and this is not representative of the dataset. it may copy an unpopular item.

The logic i have in mind is to randomly pick a number between 1 and 100. if that number is within the %Lines "band" then it looks at the first and last sku. it then picks a random number between that first and last sku, goes down the list of the source dataset, picks out that row and copies that. and does a calc to replace Current from 1 to 0 and a few more cals that i'll be able to do.

so.......for example

The above table has 300,000 records (Table A). All current product range (Current = 1).
if Random number selected = 53 it looks at Row ID 1
If random number selected = 62 it looks at Row ID 3 and so on.

so random number selected is 72, it looks at ROW ID 4, First SKU = 191, Last SKU = 290
it then picks a random number between 191 and 290, so for example 277.

it looks at ROW ID 277 in TABLE A, copies that into TABLE B and sets Current = 0. so TABLE B looks like

Row id Current Item Number lines units Cumulative %SKU %Lines First SKU Last SKU
1 1 14 39 217 217 58 50 1 60
2 1 6 5 8 225 60 54 61 120
3 1 7 5 5 230 73 59 121 190
4 1 1 1 2 232 75 63 191 290
5 1 3 1 1 233 80 73 291 333
.
.
277 1 etc etc
.
.
300000 1 etc etc <<<< last row of original TABLE A dataset
300001 0 <<<< copy of row 277 and now current = 0 as its a future product range

i've declared a value in exec statement @IncreaseBy = 50,000 so TABLE B will have 50,000 "new" copied items with Current = 0 using above logic to determined which row to copy.

What do you think??
Any help is much appreciated.

Thanks, Taj

2

It sounds like you have a decent plan. What is it that you actually need help with?

3

Hi Jeff,

Thank you for your reply.

What i want is a stored procedure. I currently have the following:

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[IncreaseDataset]    Script Date: 06/15/2015 08:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[IncreaseDataset]

@SourceTableName VARCHAR(50),
@DestTableName VARCHAR(50),
@IncreaseBy integer,
@CopyActive integer


as
begin
    EXECUTE
    (
     'SELECT * INTO '+ @DestTableName +' FROM '+@SourceTableName+';' 
 
+'INSERT INTO '+@DestTableName+'
           ([Item Number]
           ,[Description]
           ,[lines]
           ,[units]
           ,[Urgent lines]
           ,[Urgent items]
           ,[Current Zone]
           ,[forecast pick]
           ,[items on hand]
           ,[length]
           ,[width]
           ,[height]
           ,[weight]
           ,[Data Source]
           ,[Active]
           ,[Current])
SELECT TOP ('+@]IncreaseBy+')
			[Item Number]
           ,[Description]
           ,[lines]+ case when [lines] = 0 then 1 else floor([units] / [lines]) end as [Lines]
           ,[units]+ case when [lines] = 0 then 1 else floor([units] / [lines]) end as [Units]
           ,[Urgent lines]
           ,[Urgent items]
           ,[Current Zone]
           ,[forecast pick]
           ,[items on hand]
           ,[length]
           ,[width]
           ,[height]
           ,[weight]
           ,[Data Source]
           ,[Active]
           ,0 as [Current] 
FROM '+@DestTableName+'
WHERE [Active] = '+@CopyActive+'
ORDER BY NEWID()'
)
end

The exec statement as follows:

exec IncreaseDataset 
@SourceTableName = 'SkuTable', 
@DestTableName = 'SkuTableCopy', 
@IncreaseBy = 50000, -- increase sku by
@CopyActive = 1 -- 1 is active SKU, 0 non-active SKUs

The above copies the original dataset, only selecting active skus (Active = 1), and increasing the dataset by 50,000 so "SkuTableCopy" will have original set plus 50,000. BUT it selects 50,000 at random (NEWID()), i need the first posts logic, whereby it picks a random number of between 1 and 100, looks to see if it exists within a range of a set column, once it finds that row, looks at two columns, and selects a random number between that column, and then copies that row number to new table, changing Current to 0.

My above code works but unrepresentative as it can select unpopular products.

I need help in rewriting the code to use the logic i have in mind.

Much appreciate.

4

I dont have "First SKU" and "Last SKU" columns in the previous stored procedure. But they will be there.

5

Apologies for the delay but the forum isn't reporting responses via email to me even though I'm setup to have it do so.

If there aren't enough items in TableA to select 50,000 rows, are duplicates allowed??

6

Hi @JeffModen,

I think most likely duplicates will occur. 50,000 out of the original 300,000 it will happen. I'm thinking I can add in a counter for each duplicate as a separate column , so the original is set to 1, the first copy is 2, the next copy is 3, and so on. Then i could concat the item number:

original Item Number     New item Number
5695                     5695-1
5695                     5695-2
5695                     5695-3
5265                     5265-1
6665                     6665-1
6665                     6665-2
5565                     5565-1

what do you think?