duplicating records in same table for different key columns

Question

duplicating records in same table for different key columns

Richard Cranston

Hall of Fame

Points: 3680
More actions
October 24, 2008 at 2:42 pm

#214448

We have a table "Account Reference" that is keyed by "Division", and a user has entered all information for Division value "01". All this information is going to be the same for the other Divisions. What is the best way to duplicate all these records in the same table and update the Division value to "02", "03", etc?

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Using a Tally table/CTE probably. How high do you want you Division numbers?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 2

What do you mean by "Using a Tally table/CTE probably"?

I was thinking of inserting one group of Division records into a new temp table, then do an update to change the Division code, then insert the data back into the original table, and delete the temp table. I just wanted to see if there was a bes way.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Indeed. And the best way is probably by using a Tally CTE ;).

Give this article a read. http://www.sqlservercentral.com/articles/TSQL/62867/

Then, if you need our help getting that applied to your situation, please post DDL and sample data in accordance with the link in my signature.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Richard Cranston (10/27/2008)
What do you mean by "Using a Tally table/CTE probably"?

The Division numbers that you gave were in sequence which naturally suggests the use of a "Tally" or "Numbers" table which is a numeric sequencing technique (or a CTE can be substituted for the table in 2005/2008).

I was thinking of inserting one group of Division records into a new temp table, then do an update to change the Division code, then insert the data back into the original table, and delete the temp table. I just wanted to see if there was a bes way.

Even without a Tally table, you do not have to do all of that. Just Insert back into the table from a Select on a single Division of the table:

INSERT into [Account Reference] (Division, AddlCol1, AddlCol2, ...)

Select '02', AddlCol1, AddlCol2, ...

From [Account Reference]

Where Division = '01'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

What the Tablly table or CTE does in this case is to give you a way to create a a bunch of Divisions at once. For instance, this example would create Divisions '02' through '10'

WITH cteTally as (

Select Row_Number() OVER(Order by object_id) as Num

From master.sys.system_columns)

INSERT into [Account Reference] (Division, AddlCol1, AddlCol2, [...])

Select Right('0'+Cast(Num as Varchar(8)),2), AddlCol1, AddlCol2, [...]

From [Account Reference]

Join cteTally ON Num between 2 and 10

Where Division = '01'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 6

Thanks for all the feedback!!

The Division values are not in sequence, so my example was not very good.

But it is great learning about the Tally Table for duplicating sequencing and other tasks!!

I will need to look into this subject more as I have not heard of this before.

For now, I will use this simple example that I can understand:

INSERT into [Account Reference] (Division, AddlCol1, AddlCol2, ...)

Select '02', AddlCol1, AddlCol2, ...

From [Account Reference]

Where Division = '01'

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]