In this article we will discuss, How to create while loop SQL server We will be using Employee table. Below example we need to insert 1000 products to a item table using while loop.
Step 1: Create a table using the following script with data:
CREATE TABLE[dbo].[Item](
[ItemId] [int] NOTNULL,
[ItemName] [nvarchar](50) NOT NULL,
[Description] [nvarchar](50) NOT NULL,
CONSTRAINT[PK_Item] PRIMARY KEYCLUSTERED
(
[ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Step 2: Create records using the following script with data.
DECLARE @ItemId int
SET @ItemId =1
WHILE(@ItemId <= 1000)
BEGIN
INSERT INTO Item VALUES
(@ItemId, 'Product - ' + CAST(@ItemId as nvarchar(20)),
'Product - ' + CAST(@ItemId as nvarchar(20)) + ' Description')
SET @ItemId = @ItemId + 1
END
Output:
