Inserting many rows programmatically 1

[annethorne]

Hi,

I know this is very easy... I just don't know how to do it:

We have the following table named Wzip_Detail

[Wzip_Detail_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarehouseNum] [int] NULL

Wzip_Detail_ID will be automatically populated.

CountryCode will always be 1
WarehouseNum will always be 1

HOWEVER:

PostalCode for row 1 will be:
00000

PostalCode for row 2 will be:
00001

PostalCode for row 3 will be:
00002

PostalCode for the last row will be:
99999

So there will be rows 00000 to 99999 incrementally a total of 100,000 rows (I think)

How do I do this programatically in SQL Query Analyzer.
Again, I'm sure it is very easy... I just don't know how to do it yet....

Thank you so much in advance for any help you can give,
Anne

 

[SQLDenis]

Here you go

declare @D table (N varchar(1))
insert into @D (N) select 0 union all select 1 union all select 2 union all select 3 union all select 4 
insert into @D (N) select N+5 from @D

insert into Wzip_Detail (PostalCode,CountryCode,WarehouseNum)
select A.N + B.N + C.N + D.N + E.N as ZipCode,1,1 from @D A, @D B, @D C,@D D,@D E
where A.N + B.N + C.N + D.N + E.N between 1 and 99999

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

change
where A.N + B.N + C.N + D.N + E.N between 1 and 99999
to where A.N + B.N + C.N + D.N + E.N between 0 and 99999
ORDER BY 1-- this will ensure that row 1 will have 00000

I failed to notice that you want 00000 also

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[annethorne]

Thank You, Thank You, Thank You, SQLDenis!!!

Have a Great Day!
Anne

 

[gmmastros]

There are only (approximately) 43,000 US Zip Codes. You can download the list here:

http://www.cfdynamics.com/cfdynamics/zipbase/index.cfm

This download also includes latitude & longitudes for each zip code.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

This thread183-1197121 shows how you can use a SQL Server User Defined Function to calculate distances based on Latitude & Longitude.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[annethorne]

Thank you George.

What you showed us is very valuable!!!

Have a great day!
Anne