If this is for test data
ONLY you can utilise a feature of the SQL
GO
statement which allows a batch to be run multiple times. You just enclose your batch between
GO
statements.
An example:
CREATE TABLE #Example (id int identity(1,1), dat nvarchar(20))
GO
insert into #Example (dat) VALUES ('A'), ('B'),('C'), ('D'),('E')
GO 10
SELECT * FROM #example
This will give me 50 rows in the table. If you look at the output messages you can see what is happening
Beginning execution loop
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
Batch execution completed 10 times.
(50 row(s) affected)
Alternatively, using
SELECT
in the same way you have try
INSERT INTO #Example SELECT TOP 1 dat FROM #Example
GO 49
I can't remember where I first saw this but here is a link to an MSSQLTips.com article on the same
Executing a TSQL batch multiple times using GO[
^]
I cannot stress strongly enough that this technique should never find its way into Production code - it simply won't work.
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.