Inserting lots of rows programmatically

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

john nada

Here's a way to populate the PostalCode column. I would set the default value of the columns CountryCode and WarehouseNum to 1 instead of explictly inserting the value 1 each time. DECLARE @PostalCode varchar(10) DECLARE @Number int SET @Number = 0 WHILE @Number < 100000 BEGIN /* Format PostalCode */ IF LEN(@Number) = 1 BEGIN SET @PostalCode = '0000' + CAST(@Number AS varchar) END ELSE IF LEN(@Number) = 2 BEGIN SET @PostalCode = '000' + CAST(@Number AS varchar) END ELSE IF LEN(@Number) = 3 BEGIN SET @PostalCode = '00' + CAST(@Number AS varchar) END ELSE IF LEN(@Number) = 4 BEGIN SET @PostalCode = '0' + CAST(@Number AS varchar) END ELSE IF LEN(@Number) = 5 BEGIN SET @PostalCode = CAST(@Number AS varchar) END /* Insert PostalCode */ INSERT INTO Wzip_Detail(PostalCode) VALUES(@PostalCode) /* Increment number */ SET @Number = @Number + 1 END

AnneThorne

Thank you so much John for your response and your very clean programming style!!! :) Anne

Eric Dahlvang

Using REPLICATE() is a little shorter:

DECLARE @PostalCode varchar(10)
DECLARE @Number int
DECLARE @cTemp varchar(10)
SET @Number = 0

WHILE @Number < 100000
BEGIN
   /* Format PostalCode */
   SET @cTemp = CAST(@Number as VARCHAR(10)) 
   SET @PostalCode = REPLICATE('0',5-LEN(@cTemp)) + @cTemp

   /* Insert PostalCode */
   INSERT INTO Wzip_Detail(PostalCode)
   VALUES(@PostalCode)

   /* Increment number */
   SET @Number = @Number + 1
END

---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

john nada

Nice Eric!