SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create duplicate records in a table without using cursors


How to create duplicate records in a table without using cursors

Author
Message
Rhett Lowson
Rhett Lowson
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 33
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
LutzM
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96229 Visits: 13561
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Rhett Lowson
Rhett Lowson
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 33
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
John Mitchell-245523
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131919 Visits: 19201
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
LutzM
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96229 Visits: 13561
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Rhett Lowson
Rhett Lowson
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 33
Thanks, looks like a tally table is just what I need!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search