Selecting rows that don't exist physically in the database
I've totally rewritten my question because the simplicity of the previous one people were taking too literally.
The aim:
INSERT INTO X
SELECT TOP 23452345 NEWID()
This query should insert 23452345 GUIDs to the "X" table. actually 23452345 means just any possible number that is entered by user and stored in database.
So the problem is that inserting rows to a database by using
开发者_运维问答INSERT INTO ... SELECT ...
statement requires you to already have the required amount of rows inserted to database.
Naturally you can emulate the existence of rows by using temporary data and cross joining it but this (in my stupid opinion) creates more results than needed and in some extreme situations might fail due to many unpredicted reasons. I need to be sure that if user entered extremely huge number like 2^32 or even bigger the system will work and behave normally without any possible side effects like extreme memory/time consumption etc...
In all fairness I derived the idea from this site.
;WITH cte AS
(
SELECT 1 x
UNION ALL
SELECT x + 1
FROM cte
WHERE x < 100
)
SELECT NEWID()
FROM cte
EDIT:
The general method we're seeing is to select from a table that has the desired number of rows. It's hackish, but you can create a table, insert the desired number of records, and select from it.
create table #num
(
num int
)
declare @i int
set @i = 1
while (@i <= 77777)
begin
insert into #num values (@i)
set @i = @i + 1
end
select NEWID() from #num
drop table #num
Of course creating a Number table is the best approach and will come in handy. You should definitely have one at your disposal. If you need something as a one-off just join to a known table. I usually use a system table such as spt_values:
declare @result table (id uniqueidentifier)
declare @sDate datetime
set @sDate = getdate();
;with num (n)
as ( select top(777777) row_number() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2
)
insert into @result(id)
select newid()
from num;
select datediff(ms, @sDate, getdate()) [elasped]
I'd create an integers table and use it. This type of table comes in handy many situations.
CREATE TABLE dbo.Integers
(
i INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 100000 /* or some other large value */
BEGIN
INSERT dbo.Integers DEFAULT VALUES
END
Then all you need to do it:
SELECT NEWID()
FROM Integers
WHERE i <= 77777
Try this:
with
L0 as (select 1 as C union all select 1) --2 rows
,L1 as (select 1 as C from L0 as A, L0 as B) --4 rows
,L2 as (select 1 as C from L1 as A, L1 as B) --16 rows
,L3 as (select 1 as C from L2 as A, L2 as B) --256 rows
select top 100 newid() from L3
SELECT TOP 100 NEWID() from sys.all_columns
Or any other datasource that has a large number of records. You can build your own table for 'counting' functionality as such, you can use it in lieu of while loops.
Tally tables: http://www.sqlservercentral.com/articles/T-SQL/62867
精彩评论