Tutorial :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 :-)


Solution: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


Solution: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')  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »