How to insert into table with From ~ To strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert into table with From ~ To strings

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2007-12-27 : 22:38:57

Hi experts,

What's the nifty way to insert into a table with 2 given sequential strings which stands for serial number From and To?
For example, @FromSerialNo = 'AAAA0500900000'
and @ToSerialNo = 'AAAA0500900009'.
The varchar @FromSerialNo and @ToSerialNo have the same length. And their character part (the 'AAAA' part in my example) will be the same as well.

Can anyone come up with a nifty way to insert into a table
A(serialno) with SerialNo starting @FromSerialNo till @ToSerialNo. Such as:

AAAA0500900000
AAAA0500900001
AAAA0500900002
...
AAAA0500900009

Thanks!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-12-27 : 23:01:29

This will do it

declare @FromSerialNo varchar(20), @ToSerialNo varchar(20)
declare @iFromSerialNo int, @iToSerialNo int
set @FromSerialNo = 'AAAA0500900000' 
set @ToSerialNo = 'AAAA0500900009'
set @iFromSerialNo = cast(right(@FromSerialNo, 10) as int)
set @iToSerialNo = cast(right(@ToSerialNo, 10) as int)

create table #TempTable (SerialNumber varchar(20))

while @iFromSerialNo <= @iToSerialNo
begin
	insert #TempTable values ('AAAA' + right('0000000000' + cast(@iFromSerialNo as varchar(10)), 10))
	set @iFromSerialNo = @iFromSerialNo + 1
end

select * from #TempTable

bigbelly
Starting Member

39 Posts

Posted - 2007-12-28 : 00:09:32

Thanks snSQL for your contribution.

Sorry, I didn't make it clear in the first place. The length of serialno's literal part ('AAAA' part in my former example) could vary. So could its format. That means that SerialNo could be like 'A1057B05000000' ~ 'A1057B05000009'. However the @FromSerialNo and @ToSerialNo will always have the same literal part. So your way of getting its digital part is a bit rigid.

Actually I has been using similar way to do the looping for the past several years. But recently I just got a hunch that it could be solved in a much better way. That's why I'm calling for "a nifty way"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-28 : 01:24:29

DECLARE @FromSerialNo varchar(30), @ToSerialNo varchar(30)

SELECT @FromSerialNo = 'AAAA0500900000', @ToSerialNo = 'AAAA0500900009'

DECLARE @T TABLE (N int)
DECLARE @L int, @Low int, @High int

IF LEFT(REVERSE(@FromSerialNo), 16) NOT LIKE '%[^0-9]%' SELECT @L = 16
ELSE IF LEFT(REVERSE(@FromSerialNo), 15) NOT LIKE '%[^0-9]%' SELECT @L = 15
ELSE IF LEFT(REVERSE(@FromSerialNo), 14) NOT LIKE '%[^0-9]%' SELECT @L = 14
ELSE IF LEFT(REVERSE(@FromSerialNo), 13) NOT LIKE '%[^0-9]%' SELECT @L = 13
ELSE IF LEFT(REVERSE(@FromSerialNo), 12) NOT LIKE '%[^0-9]%' SELECT @L = 12
ELSE IF LEFT(REVERSE(@FromSerialNo), 11) NOT LIKE '%[^0-9]%' SELECT @L = 11
ELSE IF LEFT(REVERSE(@FromSerialNo), 10) NOT LIKE '%[^0-9]%' SELECT @L = 10
ELSE IF LEFT(REVERSE(@FromSerialNo), 9) NOT LIKE '%[^0-9]%' SELECT @L = 9
ELSE IF LEFT(REVERSE(@FromSerialNo), 8) NOT LIKE '%[^0-9]%' SELECT @L = 8
ELSE IF LEFT(REVERSE(@FromSerialNo), 7) NOT LIKE '%[^0-9]%' SELECT @L = 7
ELSE IF LEFT(REVERSE(@FromSerialNo), 6) NOT LIKE '%[^0-9]%' SELECT @L = 6
ELSE IF LEFT(REVERSE(@FromSerialNo), 5) NOT LIKE '%[^0-9]%' SELECT @L = 5
ELSE IF LEFT(REVERSE(@FromSerialNo), 4) NOT LIKE '%[^0-9]%' SELECT @L = 4
ELSE IF LEFT(REVERSE(@FromSerialNo), 3) NOT LIKE '%[^0-9]%' SELECT @L = 3
ELSE IF LEFT(REVERSE(@FromSerialNo), 2) NOT LIKE '%[^0-9]%' SELECT @L = 2
ELSE IF LEFT(REVERSE(@FromSerialNo), 1) NOT LIKE '%[^0-9]%' SELECT @L = 1

SELECT @Low = CONVERT(int, RIGHT(@FromSerialNo, @L)), @High = CONVERT(int, RIGHT(@ToSerialNo, @L))

WHILE @Low <= @High
BEGIN
INSERT INTO @T SELECT @Low
SELECT @Low = @Low + 1
END

SELECT LEFT(@FromSerialNo, LEN(@FromSerialNo) - @L) + RIGHT(REPLICATE('0', 30) + CONVERT(varchar(30), N), @L) FROM @T ORDER BY N

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 01:27:51

and also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-28 : 03:13:14

quote:
Originally posted by bigbelly

Thanks snSQL for your contribution.

Sorry, I didn't make it clear in the first place. The length of serialno's literal part ('AAAA' part in my former example) could vary. So could its format. That means that SerialNo could be like 'A1057B05000000' ~ 'A1057B05000009'. However the @FromSerialNo and @ToSerialNo will always have the same literal part. So your way of getting its digital part is a bit rigid.

Actually I has been using similar way to do the looping for the past several years. But recently I just got a hunch that it could be solved in a much better way. That's why I'm calling for "a nifty way"

I guess Mr bigbelly is looking for some solution without loops which I also want so desperately as I to have been using the loop solution till now for this kind of situation.
Well I could not provide the "nifty way" the way bigbelly wants but my approach to the "loopy way" would be like this.

declare @sample as table(Fromnum varchar(100),Tonum varchar(100))
insert into @sample(Fromnum,Tonum)
values('AAAA0500900000','AAAA0500900009')

declare @fromnum int
declare @tonum int
declare @char varchar(50)
select @char=substring(fromnum,0,patindex('%[0-9]%',fromnum)) from @sample
select @fromnum=substring(fromnum,patindex('%[0-9]%',fromnum),len(fromnum))from @sample
select @tonum=substring(tonum,patindex('%[0-9]%',tonum),len(tonum)) from @sample

while @fromnum<=@tonum
begin

select @char+ +cast(@fromnum as varchar(100))
set @fromnum=@fromnum+1

end

All SQL gurus(khtan,Peso,Harsha,madhivanan)waiting for your solution the "nifty way"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 03:28:45

Did you read the link I posted?

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-28 : 03:36:42

Yes I did read it.But I did not found any way to solve the above problem from that post where the numbers are dynamically incremented between the 2 given numbers.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-28 : 03:45:39

If you use a Tally table, you don't need a while-loop.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-28 : 04:00:30

quote:
Originally posted by Koji Matsumura

If you use a Tally table, you don't need a while-loop.

Any sample examples...?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 04:24:35

quote:
Originally posted by ayamas

quote:
Originally posted by Koji Matsumura

If you use a Tally table, you don't need a while-loop.

Any sample examples...?

declare @sample as table(Fromnum varchar(100),Tonum varchar(100))
insert into @sample(Fromnum,Tonum)
values('AAAA0500900000','AAAA0500900009')

declare @fromnum int
declare @tonum int
declare @char varchar(50)
select @char=substring(fromnum,0,patindex('%[0-9]%',fromnum)) from @sample
select @fromnum=substring(fromnum,patindex('%[0-9]%',fromnum),len(fromnum))from @sample
select @tonum=substring(tonum,patindex('%[0-9]%',tonum),len(tonum)) from @sample

select @char+ +cast(@fromnum+number as varchar(100)) from 
(
   select number from master..spt_values where type='p' 
   and number between 1 and (@tonum-@fromnum)
) as t

Madhivanan

Failing to plan is Planning to fail

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-28 : 04:27:32

quote:
Originally posted by ayamas

quote:
Originally posted by Koji Matsumura

If you use a Tally table, you don't need a while-loop.

Any sample examples...?

Script below to create a Tally table was written by Jeff Moden.

use master
GO

SELECT TOP 2147483647 IDENTITY(int, 0, 1) AS N INTO Tally FROM SysColumns A, SysColumns B, SysColumns C, SysColumns D
GO

ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (N)
GO

You can use Tally table as:

DECLARE @FromSerialNo varchar(30), @ToSerialNo varchar(30)

SELECT @FromSerialNo = 'AAAA0500900000', @ToSerialNo = 'AAAA0500900009'

DECLARE @L int, @Low int, @High int

IF LEFT(REVERSE(@FromSerialNo), 16) NOT LIKE '%[^0-9]%' SELECT @L = 16
ELSE IF LEFT(REVERSE(@FromSerialNo), 15) NOT LIKE '%[^0-9]%' SELECT @L = 15
ELSE IF LEFT(REVERSE(@FromSerialNo), 14) NOT LIKE '%[^0-9]%' SELECT @L = 14
ELSE IF LEFT(REVERSE(@FromSerialNo), 13) NOT LIKE '%[^0-9]%' SELECT @L = 13
ELSE IF LEFT(REVERSE(@FromSerialNo), 12) NOT LIKE '%[^0-9]%' SELECT @L = 12
ELSE IF LEFT(REVERSE(@FromSerialNo), 11) NOT LIKE '%[^0-9]%' SELECT @L = 11
ELSE IF LEFT(REVERSE(@FromSerialNo), 10) NOT LIKE '%[^0-9]%' SELECT @L = 10
ELSE IF LEFT(REVERSE(@FromSerialNo), 9) NOT LIKE '%[^0-9]%' SELECT @L = 9
ELSE IF LEFT(REVERSE(@FromSerialNo), 8) NOT LIKE '%[^0-9]%' SELECT @L = 8
ELSE IF LEFT(REVERSE(@FromSerialNo), 7) NOT LIKE '%[^0-9]%' SELECT @L = 7
ELSE IF LEFT(REVERSE(@FromSerialNo), 6) NOT LIKE '%[^0-9]%' SELECT @L = 6
ELSE IF LEFT(REVERSE(@FromSerialNo), 5) NOT LIKE '%[^0-9]%' SELECT @L = 5
ELSE IF LEFT(REVERSE(@FromSerialNo), 4) NOT LIKE '%[^0-9]%' SELECT @L = 4
ELSE IF LEFT(REVERSE(@FromSerialNo), 3) NOT LIKE '%[^0-9]%' SELECT @L = 3
ELSE IF LEFT(REVERSE(@FromSerialNo), 2) NOT LIKE '%[^0-9]%' SELECT @L = 2
ELSE IF LEFT(REVERSE(@FromSerialNo), 1) NOT LIKE '%[^0-9]%' SELECT @L = 1

SELECT @Low = CONVERT(int, RIGHT(@FromSerialNo, @L)), @High = CONVERT(int, RIGHT(@ToSerialNo, @L))

SELECT LEFT(@FromSerialNo, LEN(@FromSerialNo) - @L) + RIGHT(REPLICATE('0', 30) + CONVERT(varchar(30), N), @L)
FROM master.dbo.Tally
WHERE N BETWEEN @Low AND @High
ORDER BY N

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-28 : 04:35:18

quote:

Originally posted by madhivanan

quote:
Originally posted by ayamas

quote:
Originally posted by Koji Matsumura

If you use a Tally table, you don't need a while-loop.

Any sample examples...?

declare @sample as table(Fromnum varchar(100),Tonum varchar(100))
insert into @sample(Fromnum,Tonum)
values('AAAA0500900000','AAAA0500900009')

declare @fromnum int
declare @tonum int
declare @char varchar(50)
select @char=substring(fromnum,0,patindex('%[0-9]%',fromnum)) from @sample
select @fromnum=substring(fromnum,patindex('%[0-9]%',fromnum),len(fromnum))from @sample
select @tonum=substring(tonum,patindex('%[0-9]%',tonum),len(tonum)) from @sample

select @char+ +cast(@fromnum+number as varchar(100)) from 
(
   select number from master..spt_values where type='p' 
   and number between 1 and (@tonum-@fromnum)
) as t

Madhivanan

Failing to plan is Planning to fail

Thats great work madhivanan.
I guess Mr bigbelly got the solution the way he wanted the "nifty way" & so did I.
Cheers..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 04:36:01

For small set of data, you can make use of master..spt_values table

Madhivanan

Failing to plan is Planning to fail

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-28 : 04:42:37

quote:

Originally posted by madhivanan

quote:
Originally posted by ayamas

quote:
Originally posted by Koji Matsumura

If you use a Tally table, you don't need a while-loop.

Any sample examples...?

declare @sample as table(Fromnum varchar(100),Tonum varchar(100))
insert into @sample(Fromnum,Tonum)
values('AAAA0500900000','AAAA0500900009')

declare @fromnum int
declare @tonum int
declare @char varchar(50)
select @char=substring(fromnum,0,patindex('%[0-9]%',fromnum)) from @sample
select @fromnum=substring(fromnum,patindex('%[0-9]%',fromnum),len(fromnum))from @sample
select @tonum=substring(tonum,patindex('%[0-9]%',tonum),len(tonum)) from @sample

select @char+ +cast(@fromnum+number as varchar(100)) from 
(
   select number from master..spt_values where type='p' 
   and number between 1 and (@tonum-@fromnum)
) as t

Madhivanan

Failing to plan is Planning to fail

Your solution will not work if the range is 'A1057B05000000' ~ 'A1057B05000009'.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-28 : 04:53:40

Yup you are right.

bigbelly
Starting Member

39 Posts

Posted - 2007-12-28 : 07:45:51

Thanks guys for all of your contributions.

The reason I said recently I had a hunch this issue could be solved in a nifty way is because I read an article several days ago at
http://sqlserver-qa.net/blogs/t-sql/archive/2007/12/18/2975.aspx

Just now I just found out the author is madhivanan. Great ;-)

The madhivanan's solution is pretty close to my definition of "nifty way" already

, just need to tweak the script a bit to make it work for case 'A1057B05000000' ~ 'A1057B05000009'. I'm just feeling a bit lazy to think about it at friday night 11:50 PM. ;-)

As for table master..spt_values could only provide 2048 numbers, there is a very cool UDF from book <<Inside Microsoft SQL Server 2005 T-SQL Querying>> Chapter 4.


CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH 
L0   AS (SELECT 1 AS C UNION ALL SELECT 1),
L1   AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2   AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3   AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4   AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5   AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT n FROM Nums WHERE n<@n

using the above UDF, you could easily get a table with 1 million numbers. Replacing the table master..spt_values with this UDF in madhivanan's code, then it could slove extreme case such as 'AAAA0500000000' ~ 'AAAA0500999999'

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-29 : 19:36:19

Code only returns 999,999 numbers without the missing "=" sign... should be...

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH 
L0   AS (SELECT 1 AS C UNION ALL SELECT 1),
L1   AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2   AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3   AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4   AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5   AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT n FROM Nums WHERE n<=@n

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-31 : 04:43:37

quote:

Originally posted by Jeff Moden

Code only returns 999,999 numbers without the missing "=" sign... should be...

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH 
L0   AS (SELECT 1 AS C UNION ALL SELECT 1),
L1   AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2   AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3   AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4   AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5   AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT n FROM Nums WHERE n<=@n

--Jeff Moden

If it is SQL Server 2005, my favourite is

with numbers(n)
as
(
select 1 as n
union all
select n+1 from numbers where n<1000000
)
select n from numbers option(maxrecursion 0)

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-31 : 13:12:14

You may want to get a new favorite... it takes a very long time to create a million numbers...

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 168 ms.

SQL Server Execution Times:
   CPU time = 33844 ms,  elapsed time = 41479 ms.

Itzek's function is actually just a tiny bit slower than my "favorite" although mine does have a bit of disk access...

SQL Server parse and compile time: 
   CPU time = 93 ms, elapsed time = 130 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
===== Itzek's Function ====================================================================

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 907 ms,  elapsed time = 958 ms.
===== Jeff's Function =====================================================================

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'syscolrdb'. Scan count 2, logical reads 110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 29, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 789 ms.
====================================================================================================

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Here's the code for the test times above...

 CREATE FUNCTION dbo.fnItzekNumsTest(@N AS BIGINT) 
RETURNS TABLE
     AS
 RETURN
   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
        L4   AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
        L5   AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
        Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
         SELECT N FROM Nums WHERE n <= @N

GO
 CREATE FUNCTION dbo.fnJBMNumsTest(@N AS INT) 
RETURNS TABLE
     AS
 RETURN
 SELECT TOP (@N) 
    N = ROW_NUMBER() OVER(ORDER BY sc1.Object_ID)
  FROM sys.all_columns sc1,
       sys.all_columns sc2
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @N INT
PRINT '===== Itzek''s Function ===================================================================='
SELECT @N = N
  FROM dbo.fnItzekNumsTest(1000000)
PRINT '===== Jeff''s Function ====================================================================='
SELECT @N = N
  FROM dbo.fnJBMNumsTest(1000000)
PRINT REPLICATE('=',100)

SET STATISTICS TIME OFF
SET STATISTICS IO ON

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 00:50:17

Yes it is. Thanks for the testing

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources