SQL Query : How to loop with inserting similar data

Question

I have a SQL database (sdf file)

I would like to create dummy records to fill in the database for a fixed size of say 22 MBytes.

I have a simple insert command:

INSERT INTO Log (Time, Source, MyID, SubID, Description) VALUES ('2009-09-17 01:20:35', '1', '1', '60', 'Test Record')

What I need to do is be able to create a loop which does this insert until the database file reaches a fixed size i.e. 22 Megs.

Please can you help with sample code. I am using server explorer for my database connection. I was unable to install SQL server so cannot use management studio :-(

I need to add in the command in a single query please :-)

Answer 1

If you simply want to execute this one command multiple times, try this:

INSERT INTO Log (Time, Source, MyID, SubID, Description) 
VALUES ('2009-09-17 01:20:35', '1', '1', '60', 'Test Record')
GO 255

If you specify any number after the "GO", it will be executed that many times in succession.

Marc

Answer 2

You can play around with this to check for table size etc rather than database size. Or to aggregate data file sizes if you multiple files.

WHILE (SELECT size/128 FROM sys.database_files WHERE file_id = 0) < 22
    INSERT INTO Log (Time, Source, MyID, SubID, Description)
    VALUES ('2009-09-17 01:20:35', '1', '1', '60', 'Test Record')