INSERT of consecutive numbers
-
Simple question:
I want to insert zip codes into a table that are in consecutive order (10001-10099). What is the easier way (if any) to insert all of these than typing them in one at a time?
-
build a temp table with an identity col and populate from some known record count
SELECT IDENTITY( INT, 10000, 1 ) AS id, ' ' AS JUNK
INTO #t
FROM SYSOBJECTS
INSERT INTO #t SELECT ' ' FROM #t
repeat until you have enough numbers, then join and update your destination table
-
>>into a table that are in consecutive order
There is no concept of "order" in a table, you're applying file-based thinking to a relational system.
You insert them to the table unordered and pull them out in whatever order you require using ORDER BY in your SELECT statements,
-
John thanks for your post.PW thanks for your post too but that wasn't what I was asking. Sorry if I came across the wrong way. I am trying to insert numbers 10001 to 100099 (100 numbers) into a table. Just don't want to do it manually if I don't have too. (I have other numbers in consecutive order that I must enter in later.) Just thought there would be something with the INSERT statement that would allow values with a range to be inserted. Thank you again!
-
oops! typo. Suppose to be 10001 to 10099 (100 numbers) not 100099 (thank god!)
-
Select 10000 + (10*a.n + b.n) as number
into #T
from
(
select 0 as n
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
)a
cross join
( select 0 as n
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
)b
* Noel -
Thanks much- gonna tool around with this!!!!!
-
Glad to help !
* Noel -
FWIW, here are two other variations
CREATE TABLE #num
(
nbr INT
)
INSERT INTO #num
SELECT Number+10001
FROM master..spt_values
WHERE type='P' AND Number<100
SELECT * FROM #num
DROP TABLE #num
You can also use your own number table. Pretty handy in many situations. Or:
SELECT
TOP 100 num = IDENTITY(INT,10001,1)
INTO
#num
FROM
Sysobjects S1
CROSS JOIN
Sysobjects S2
SELECT * FROM #num
DROP TABLE #num
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm very much in favour of having a 'numbers' table. You can easily have a 'topup' function if you think you might need to use a bigger number than what you have.
Of course, it'll be much easier in SQL2005 when you have 'rownumber' introduced. Because then you'll be able to query any table with enough rows in it, and just refer to 'rownumber' to get numbers from 1 up. But really, that just means you'll be able to have a table with nulls in it.
select *
FROM master..spt_values
where type = 'P'
will do for numbers between 0 and 255. But then if you want more than that, you might need your own table.
RobF
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
This mightmare table master..spt_values has also been extended to 2048 (unless I am mistaken) in the next version.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If I read the posted problem correct, it was 'how to insert 100 rows with incrementing values in an "easy" way'..
Here's another variant I use whenever it's a one-time op (like I assume this is)
declare @i int
set @i = 10001
while @i <= 10099
begin
insert myTab ( col1 ) values ( @i )
set @i = @i + 1
end
Not relational, but easy to read
/Kenneth
-
Just to show there are "many ways to skin a cat", you could also create the list in a spreadsheet first and then import it into SQL server with DTS. For example in Excel, if you enter the number 10001 in cell A1 and 10002 in cell A2, then highlight the 2 cells and click and drag down on the bottom right corner, it will create a sequential list. When you have the list you want, save it and then import it by right-clicking on your database in Enterprise Manager and selecting "All Tasks" > "Import Data". If this is a one-time data population it's all you need. Maybe not the most elegant, but probably faster than writing code to do it.
Andrew
-
Find a C/C++ programmer and have him/her create an Extended Stored Procedure that takes 1 parameter; the number of single-column (an Int) rows to return. The column will contain the 0-based incrementing count value [i.e., row column ranges from 0 to input parameter - 1].
T-SQL:
Create Table #Tmp(Num Int Primary Key)
Insert #Tmp
Exec master.dbo.XPROC 1000
Declare @Offset Int
Set @OffSet=100001
Select Num + @Offset[The Column I really Wanted] into MyTable from #Tmp
...
There are many variations on this theme...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply