December 29, 2010 at 3:05 pm
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'.
December 29, 2010 at 4:02 pm
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"....;-)
December 30, 2010 at 6:20 am
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?
December 30, 2010 at 6:39 am
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
December 30, 2010 at 6:43 am
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
December 30, 2010 at 6:51 am
Thanks, looks like a tally table is just what I need!
July 21, 2023 at 8:39 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply