Duplicating Certain number of rows

Question

Duplicating Certain number of rows

jwillbanks

SSC Veteran

Points: 258
More actions
February 15, 2013 at 9:08 am

#289471

I've got a table with shipping information that is grouped by the item shipped and includes several columns of data for each item. What I'm trying to do is replicate future growth by adding a certain number of additional rows by using current rows and only adding a letter to the item number. This would signify a new item and it would have the exact data as the original. Is this possible?
Thanks,
Justin

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

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

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 1

jwillbanks (2/15/2013)
I've got a table with shipping information that is grouped by the item shipped and includes several columns of data for each item. What I'm trying to do is replicate future growth by adding a certain number of additional rows by using current rows and only adding a letter to the item number. This would signify a new item and it would have the exact data as the original. Is this possible?
Thanks,
Justin

A bit more detail would be helpful. What do the original and new items look like? What determines how many dupes you want to generate? What is the datatype of the column that you want to add the letters to?

Please see the first link in my signature line below for much better help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jwillbanks SSC Veteran Points: 258 More actions · Answer 2

A typical item number is 100640 and is setup as float. We have a growth percentage that will determine how many rows to duplicate. I can also just figure out the number, as well. The new item would look like 100640A.

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 3

So how do you expect to store "100640A" in your database as a FLOAT? That just can't happen. You'll need to change the column to VARCHAR and that's going to have some serious ripple effect throughout your database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jwillbanks SSC Veteran Points: 258 More actions · Answer 4

It doesn't have to be an A that's appended to the item number. A 1 would work, as well.

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 5

You still haven't answered the other questions. What determines how many dupes you want to add? What is the datatype of the column you want to do this to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jwillbanks SSC Veteran Points: 258 More actions · Answer 6

I thought I already answered that with the float and growth percentage response above. Percent can be anything for demonstration purposes.

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 7

Yeah... my bad. You did. I guess I just can't believe that someone designed the database to use a FLOAT datatype for such a thing. It's limited to 15 digits and then become imprecise after that. If we add a digit or two to those numbers, is it going to exceed 15 digits? Guess that's up to you to figure out.

I also know nothing about your "Growth Percentage". Percentage of what? What is the calculation that you want that uses this to determine the actual number of rows you want to duplicate? Do you want all the rows duplicated or what?

Again, guess that's up to you to figure out.

That, notwithstanding, here's a method to duplicate rows. I start out with 1000 and duplicate each row 15 times.

--===== Build some test data. This is not a part of the solution

SELECT TOP 1000

SomeFloatColumn = CAST(ROW_NUMBER()OVER(ORDER BY (SELECT NULL))+10000 AS FLOAT),

SomeOtherColumn = NEWID() --Just to have something else in the table.

INTO #YourTable

FROM sys.all_columns ac1

;

--===== Make 15 copies of each row adding an incremental suffix of 2 digits to each SomeFloatColumn.

WITH

cteTally(N) AS

(

SELECT TOP (15) --This is the number that controls the number of copies

CAST(ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS FLOAT)

FROM sys.all_columns

)

INSERT INTO #YourTable

(SomeFloatColumn,SomeOtherColumn)

SELECT SomeFloatColumn = yt.SomeFloatColumn * 100 + t.N,

yt.SomeOtherColumn

FROM #YourTable yt

CROSS JOIN cteTally t

;

select * from #yourtable

As you can see by looking at the content of the table after we added the rows, there's going to be an ever increasing chance of unwanted duplication of the numbers.

I still stand by the recommendation that adding the "dupe" number to the original number is a bad idea and that storing this information as a FLOAT is a worse idea. I strongly recommend that you change the table so the column is a VARCHAR or, if you want to keep the FLOAT, that you make a new column to hold the "dupe" number in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)