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'.
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"....;-)
For example, I have a single competition entry for the following people that appears in this Entries table:
EntryID Email EntryDate FirstName LastName
1 firstname.lastname@example.org 12/28/2010 12:23:45 PM Gabriel Markov
2 email@example.com 12/28/2010 12:25:01 PM Marie Smith
3 firstname.lastname@example.org 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 email@example.com 12/28/2010 12:27:25 PM Marie Smith
5 firstname.lastname@example.org 12/28/2010 12:27:25 PM Marie Smith
6 email@example.com 12/28/2010 12:27:25 PM Marie Smith
7 firstname.lastname@example.org 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?
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 (
, '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
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
INSERT INTO @tbl
SELECT 2,'email@example.com','12/28/2010 12:25:01 PM','Marie','Smith'
DECLARE @id 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
( SELECT *
FROM master..spt_values m
WHERE m.number <= @nmbr_of_accounts AND m.number > 0 and m.Type = 'P'
Thanks, looks like a tally table is just what I need!
Viewing 6 posts - 1 through 5 (of 5 total)