开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜