How to create duplicate records in a table without using cursors

  • Rhett Lowson

    Mr or Mrs. 500

    Points: 552

    Do a search on the Internet for 'How to create duplicate records in a table' and you get millions of results on how to remove duplicate records from a table. So let me first provide a reason why I might want to do something like this:

    I'm running a contest and an entrant gets their first entry free. Then they get an option to get additional entries for inviting friends, making suggestions or whatever, but they get a variable number of additional entries. Each entry is a separate record. I know I could possibly add a field for NumberOfEntries and just update it, but that's not how the database has been designed.

    I can get this process to work using cursors, but in the interest of doing things the optimal way, i.e. not using cursors, is this possible without a whole lot of of extra work?

    My sample table is called 'Entries' and it has field names 'EntryID', 'Email', 'FirstName' and 'LastName'.

  • LutzM

    SSC Guru

    Points: 107049

    Would you mind providing some sample data (including expected result) so w can see what you're looking for?

    Your verbal description "leaves some room for interpretation"....;-)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Rhett Lowson

    Mr or Mrs. 500

    Points: 552

    For example, I have a single competition entry for the following people that appears in this Entries table:

    EntryID Email EntryDate FirstName LastName

    1 123@xyz.com 12/28/2010 12:23:45 PM Gabriel Markov

    2 maries@test.com 12/28/2010 12:25:01 PM Marie Smith

    3 j_smith@test.com 12/28/2010 12:26:30 PM John Smith

    A few minutes later Marie invites 4 of her friends to enter too and so she gets an extra four entries in one go:

    4 maries@test.com 12/28/2010 12:27:25 PM Marie Smith

    5 maries@test.com 12/28/2010 12:27:25 PM Marie Smith

    6 maries@test.com 12/28/2010 12:27:25 PM Marie Smith

    7 maries@test.com 12/28/2010 12:27:25 PM Marie Smith

    My script currently reads her initial entry and uses a cursor to insert new records for each entry. EntryID is an identity fields that auto-increments and the date comes from getdate(), all the other data is duplicated from the original entry.

    Marie's friends like to spend their lunch hour online, and so the site gets quite heated for the next hour while her friends enter as well and invite their friends along too. Given the method I'm using, is this the best, or is there a better way that does not use a cursor?

  • John Mitchell-245523

    SSC Guru

    Points: 148431

    The best way is to create a "numbers" or "tally" table - search this site for how to do that, or you can use master.dbo.spt_values as long as you won't need values above a certain number.

    Now you can do something like this (not tested, so please tweak it if it contains errors):

    ;WITH Entry AS (

    SELECT

    'maries@test.com' Email

    , '12/28/2010 12:27:25 PM' EntryDate

    , 'Marie' FirstName

    , 'Smith' LastName

    )

    INSERT INTO Entries (Email, EntryDate,FirstName, LastName)

    SELECT e.Email, e.EntryDate, e.FirstName, e.LastName

    FROM Entry e

    CROSS JOIN Tally t

    WHERE t.Number <= 4 -- or however many entries you are inserting

    John

  • LutzM

    SSC Guru

    Points: 107049

    If you don't have a tally table already (which is very useful in several cases) you could take the "master..spt_values" apporach:

    DECLARE @tbl TABLE

    (

    EntryID INT,

    Email VARCHAR(100),

    EntryDate DATETIME,

    FirstName NVARCHAR(50),

    LastName NVARCHAR(50)

    )

    INSERT INTO @tbl

    SELECT 2,'maries@test.com','12/28/2010 12:25:01 PM','Marie','Smith'

    SELECT *

    FROM @tbl

    DECLARE @id INT,

    @nmbr_of_accounts INT

    SELECT @id=2, -- Marie's account

    @nmbr_of_accounts = 4 -- no of accounts to create

    SELECT t.Email, GETDATE(),FirstName,LastName

    FROM @tbl t

    CROSS APPLY

    ( SELECT *

    FROM master..spt_values m

    WHERE m.number <= @nmbr_of_accounts AND m.number > 0 and m.Type = 'P'

    ) x

    WHERE t.EntryID=@id



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Rhett Lowson

    Mr or Mrs. 500

    Points: 552

    Thanks, looks like a tally table is just what I need!

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

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