>>>>Hi,
>>>>
>>>>I need to generate 20 million records in a table in a database in order to make some realistic testing situations. I created some VFP code which created about 4.5 million records but this took about 5 hours. I am now trying some SQL code to SELECT the top 1000 records and insert them back into the table (with new GUID primary key) and looping many times, but this is going pretty slowly too.
>>>>
>>>>What would you all suggest? Would selecting all 4.5 million and inserting them back into the table be faster? And then maybe do that again a few times?
>>>
>>>20K batch insert should be fast enough.
>>
>>Are you suggesting something like this:
>>
>>
USE [RESREPRICERTest]
>>GO
>>declare @cnt int = 0;
>>
>>while @cnt < 100
>>begin
>>
>>INSERT INTO [dbo].[Transactions]
>> (
>> [DateImported]
>> ,[OID])
>>SELECT top 20000 [DateImported]
>> ,[OID]
>> from dbo.[Transactions];
>>
>> set @cnt = @cnt + 1;
>> Print @cnt;
>>end
>
>In your code I don't see a way to load new rows every time. But in general, yet, that's the idea.
Thanks, this is still pretty slow though :(
BTW, the INSERT INTO ... SELECT TOP 20000 adds the new rows.