Script to continuously insert data

Question

Post reply

Script to continuously insert data

passivebyz

SSCommitted

Points: 1908
More actions
May 30, 2010 at 2:48 pm

#221625

Hi,
I want test live migration feature of SQL Serve 2008 R2 and for which I need a script which inserts data continuously in a loop. Please advice.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please be a little more specific regarding the table/data structure you want to insert.

Otherwise, you might see a script like the following to enter a few thousand rows into a table:

SELECT number

INTO target_table

FROM master..spt_values where type='P'

I'm not sure if that's what you're looking for though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

passivebyz SSCommitted Points: 1908 More actions · Answer 2

Thanks you,

I just want to create a new table and insert data in loop using a query from management studio and want to see while performing live migration, whether the migration interrupts the query running from Management studio or not.

please advice

thanks

Marco Default port Points: 1408 More actions · Answer 3

Is this what you want?

CREATE TABLE [Test] (col1 bit)

WHILE 1=1

BEGIN

INSERT INTO [Test] VALUES (1)

END

DROP TABLE [Test]

Regards,

Marco

Lowell SSC Guru Points: 323518 More actions · Answer 4

CREATE TABLE [Test] (col1 bit)

DECLARE @endDate datetime

SET @endDate = DATEADD(hh,8,GETDATE()) -- 8 hours from now

WHILE GETDATE() < @endDate

BEGIN

INSERT INTO [Test] VALUES (1)

END

DROP TABLE [Test]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 5

Lowell (6/1/2010)
CREATE TABLE [Test] (col1 bit)
DECLARE @endDate datetime
SET @endDate = DATEADD(hh,8,GETDATE()) -- 8 hours from now
WHILE GETDATE() < @endDate
BEGIN
INSERT INTO [Test] VALUES (1)
END
DROP TABLE [Test]

Are you really recommending to run that code?

Assuming the performance of my PC it would generate a few hundred million rows... :pinch:

(side note: I used a 10sec loop and extrapolated the result...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Marco Default port Points: 1408 More actions · Answer 6

The poster wants to know if the migration interrupts the insert query which is running. This was a query which continuously inserts data.

You can also immediately delete the data after the INSERT stament. (and change recovery model of the database to SIMPLE)

CREATE TABLE [Test] (col1 bit)

DECLARE @endDate datetime

SET @endDate = DATEADD(hh,8,GETDATE()) -- 8 hours from now

WHILE GETDATE() < @endDate

BEGIN

INSERT INTO [Test] VALUES (1)

DELETE FROM [Test]

END

DROP TABLE [Test]

Regards,

Marco

passivebyz SSCommitted Points: 1908 More actions · Answer 7

thanks all,

it helped me and I just added wait for delay 2 sec

CREATE TABLE [Test] (ID bit)

DECLARE @endDate datetime

SET @endDate = DATEADD(hh,1,GETDATE()) -- 1 hours from now

WHILE GETDATE() < @endDate

BEGIN

INSERT INTO [Test] VALUES (1)

WAITFOR DELAY '00:00:02';

END