April 30, 2014 at 5:55 am
create table #sample (rowguid int identity ,id_frm int ,id_to int)
insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)
In the above table I have values starting Id and Ending Id. I need to prepare a table which has all the numbers falls between starting Id and Ending Id
i have tried it with looping but response is very slow in real world.
any body help me with query ???
This is what I have tried so far...
declare @sql varchar(8000) = '
select top '+cast((select max(id_to) from #sample) as varchar(100))+' identity(int,1,1) as guidid into tally from sysobjects,syscolumns '
exec (@sql)
alter table Tally add slab varchar(10)
create clustered index idx on Tally(guidid)
create clustered index idx on #sample(id_frm asc,id_to desc)
update Tally set slab = rowguid
from #sample join Tally on guidid between id_frm and id_to
delete from Tally where slab is null
select * from Tally
This query works fine with small numbers
But My real time table have 13 digit nos. It through Arithmetic overflow error
April 30, 2014 at 6:07 am
vignesh.ms (4/30/2014)
...But My real time table have 13 digit nos. It through Arithmetic overflow error
Please provide some realistic sample data so that folks attempting to help won't have the same problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 30, 2014 at 6:09 am
Use bigint. And you don't need the dynamic SQL:
create table #sample (rowguid bigint identity ,id_frm bigint ,id_to bigint)
insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)
select top((select max(id_to) from #sample)) identity(bigint,1,1) as guidid into tally from sysobjects,syscolumns
April 30, 2014 at 6:39 am
ChrisM@Work (4/30/2014)
Please provide some realistic sample data so that folks attempting to help won't have the same problem.
Table structure is same... Data only differs
It will always starts and ends with 13 digit number ...
Here are the sample records to insert
insert into #sample values( 1254858693000,1254858693999)
insert into #sample values( 9867668925500,9867668929999)
April 30, 2014 at 6:43 am
DROP table #sample
create table #sample (rowguid int identity ,id_frm int ,id_to int)
insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)
insert into #sample values( 30,1962)
SELECT
id_frm+n, -- n is BIGINT, result is BIGINT
*
FROM #sample
CROSS APPLY (
SELECT TOP(id_to-id_frm+1)
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n), -- This iTally provides
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n), -- 10000 rows MAX.
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c (n), -- expand as necessary
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)
) iTally (n)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 30, 2014 at 6:57 am
I just changed your table slightly:
create table #sample (rowguid int identity ,id_frm bigint ,id_to bigint)
using bigint instead of int. With that in place, the rest of your queries work fine for me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply