February 15, 2013 at 9:08 am
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
February 15, 2013 at 9:39 am
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
Change is inevitable... Change for the better is not.
February 15, 2013 at 10:34 am
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.
February 15, 2013 at 11:42 am
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
Change is inevitable... Change for the better is not.
February 15, 2013 at 11:44 am
It doesn't have to be an A that's appended to the item number. A 1 would work, as well.
February 15, 2013 at 3:38 pm
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
Change is inevitable... Change for the better is not.
February 15, 2013 at 7:31 pm
I thought I already answered that with the float and growth percentage response above. Percent can be anything for demonstration purposes.
February 16, 2013 at 2:19 pm
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply