Click here to Skip to main content
14,599,610 members
Rate this:
Please Sign up or sign in to vote.
See more:
Is it possible in SQL server to run SQL statements nth times. I want to run the belwo statement 50 times..

insert into [IMSIdentity] 

select *
from [IMSIdentity] 


What I have tried:

I tried CROSS APPLY statement without luck
Posted
Updated 23-Jun-20 2:20am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

That's probably a very bad idea, but - it's your database that going to be filled with at least 562,949,950,000,000 rows, all of them duplicates - so see here: SQL Server: FOR LOOP[^]

Note: the operation may take some time to execute ...
   
v2
Comments
Maciej Los 23-Jun-20 8:30am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can use Cursor[^] or CTE[^].

ExXample - using CTE:
DECLARE @tmp TABLE(MyId int)
DECLARE @maxId int = 50

;WITH CTE AS
(
    --initial value
    SELECT 1 AS MyId
    --recursive part
    UNION ALL
    SELECT MyId +1
    FROM CTE
    WHERE MyId<@maxId
)
INSERT INTO @tmp (MyId)
SELECT MyId
FROM CTE

SELECT *
FROM @tmp


Result:
1
2
...
49
50
   
Comments
CHill60 23-Jun-20 8:07am
   
Nice use of a recursive CTE. 5
Maciej Los 23-Jun-20 8:27am
   
Thank you, Caroline.
Rate this:
Please Sign up or sign in to vote.

Solution 3

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  -- This one is important otherwise it tries to create the table again
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.
   
Comments
Maciej Los 23-Jun-20 8:30am
   
5ed!
Jörgen Andersson 23-Jun-20 15:46pm
   
Cool

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100