Duplicating table rows in a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicating table rows in a stored procedure

Author

Topic

Reckless
Starting Member

2 Posts

Posted - 2002-04-18 : 19:31:44

Hi all,

I'm a complete novice at SQL Server so please can anyone help.

I'd like to take a table of 3 million rows and duplicate the records upto a user specified limit (working on 150 million). It's for load testing an application!

The table is simple and so the task 'should' be easy ?! I had thought about a design and think a stored procedure would be possible with something like this:

create procedure generatedata RequireCount int
as
(I can do this bit!):
count current records into local variable
determine number of duplicates per record
so required count of 150million, current count = 3 million
I would therefore need to generate 50 duplicates per source record

select into a target table all current rows (3 mil)

(Stuff I'm not sure how to best do!):

create a cursor of all records in source table (3 mil)
for each row in the source table

while index < duplicate count per row (1...50)

extract a known field & update it's value
extract another known field & update it's value

generate an insert statement for the target table
I DO NOT know all the fields (I only know the above 2) - it's supposed to be sort of generic.
I was hoping to create a cursor or something from sp_columns or similar....

execute insert statement against target table using mix of source table column data and modified data (above two fields)

increment index

end while

end for

Any assistance would be greatly appreciated!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-18 : 19:54:27

Reckless,

The bit which will determine the best cause is...

quote:

extract a known field & update it's value
extract another known field & update it's value

What value??

You could go with something like this.. (avoiding cursor)


@Increment int = 0
@Total int = 50
while @Increment < @Total
	Update Table set Field1 = Field1 + @Increment, Field2 = Field2 +  @Increment
	Insert TableTemp
		Select * from Table 
	@Increment = @Increment + 1
loop

HTH

DavidM

Tomorrow is the same day as Today was the day before.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 20:01:26

OK, DON'T use a cursor. If you do, it will never finish running.

Using a loop (NOT, REPEAT NOT, a cursor) that performs the INSERT however many times, this elaborates on David's suggestion:

CREATE PROCEDURE GenerateData @RequireCount int AS
DECLARE @mult int, @ctr int, @remainder int

SELECT @mult=@RequireCount/COUNT(*), @remainder=@RequireCount%Count(*), @ctr=2
FROM SourceTable

SELECT * INTO TargetTable FROM SourceTable

WHILE @ctr<=@mult
BEGIN
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1 + @ctr, Col2 + @ctr, Col3 + @ctr FROM SourceTable
END

IF @remainder>0
BEGIN
SET ROWCOUNT @remainder
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1 + @ctr, Col2 + @ctr, Col3 + @ctr FROM SourceTable
SET ROWCOUNT 0
END

This will also find the remainder rows (let's say they wanted 151 million) and insert them too. If that's not a requirement, you can remove everything from the "IF @remainder>0" to the end.

You could modify the INSERT...SELECT portion to create random numbers or character values, you get the idea. If you did it as an INSERT and an UPDATE, you'll just exaggerate the problem you are going to have with...

THE TRANSACTION LOG. This will fill up your transaction log pretty quickly, just doing the INSERTs. There might be a way of doing this as a single INSERT job, possibly using the SELECT...INTO operation once. If so, then you can minimally log the operation and it would run much faster. I need to know more about what kind of data manipulation you need to do on the test data before I can offer a solution though.

I know it's supposed to be generic, but unless you have tables with very similar structures, you should use this as a template and modify it as needed for individual tables, instead of trying to tailor it to handle every kind of table automatically.

Edited by - robvolk on 04/18/2002 20:02:07

Reckless
Starting Member

2 Posts

Posted - 2002-04-19 : 04:23:08

Wow - great forums here :)

The source and target tables are exactly the same. I'd like to create the target table on the fly using the source table as a template.

The values of the fields to be updated are not random but more 'intelligently updated':
One field is a unique reference number. Update to this field would need to be take existing max, and add this to an index value in the loop such that it justs increments.

Another field is a date. This date can be modified by simply adding an offset to it so the record looks unique. It's not a key field so it wouldn't matter that much if the date did clash. The offset would be a few years so it's unlikely to anyway.

I'll now read over the solutions provided - one thing I've DEFINATELY picked up is that using a cursor is BAD! I was just thinking that is was a requirement based upon my field updates? Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources