Problem
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?
Solution
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.
I need to load very fastly some sample data to database table with Sequential Numbers. We can load Sequential Numbers with Loops but it is a performance issue because it will load one record in one iteration . What is an alternative for loops to load Sequential Numbers data into table ?
Solution
By using iteratative Common table exepresion(CTE) , we can able to get the results very fastly . In the below query every CTE will sequres the amount of result come from previous CTE results.
CREATE TABLE #T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL)
CREATE INDEX Ix ON #T1 (X, Id)
;WITH
Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows
Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows
Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows
Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows
Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows
Cte AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS Number FROM Pass5)
INSERT INTO #T1
SELECT Number, Number
FROM Cte
WHERE Number <= 100000
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
