Sunday, October 13, 2013

While Loop and Variable Casting in SQL Script


Sometimes ago, I needed to insert bulk data to one of my table. I was needed a loop which will insert the data one by one. Previously I did that using a console application. But now I find a better solution using SQL Script. Below is the SQL I used. It is self explanatory.


DECLARE @i int = 0
declare @suffix varchar (20)
WHILE @i < 20 BEGIN
    SET @i = @i + 1
    set @suffix = CAST(@i AS varchar (20))
    /* do some work */
    INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[Phone]
           ,[Email])
     VALUES
           ('F'+@suffix
           ,'L'+@suffix
           ,'Ph'+@suffix
           ,'Em'+@suffix
           )
END


Those who are interested to test in your environment, use the below sql to create the table quickly.

CREATE TABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[Phone] [varchar](50) NOT NULL,
[Email] [varchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]