duplicating records in same table for different key columns

  • 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?

  • 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]

  • 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.

  • 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]

  • 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]

  • 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]

  • 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'

  • 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]

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

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