loop with a insert statement in a script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

loop with a insert statement in a script

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-02-01 : 16:10:35

Hi,

I have this code here for creating a table and then populating that table with a record.
Now i want to create a variable called rec_count at the top.
when populating the table i want to create rows which equal to the number in rec_count.
I want to put ths insert statement in a loop like

declare rec_count int
while i < rec_count
INSERT [dbo].[tbl_RecordNum] ([Rec_ID], [Rec_NM], [Rec_TM]) VALUES (1, NULL, NULL)
end while

Is this possible?
How can i do this?

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_RecordNum]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_RecordNum]
GO
/****** Object: Table [dbo].[tbl_RecordNum] Script Date: 02/01/2010 14:39:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_RecordNum]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_RecordNum](
[Rec_ID] [int] IDENTITY(1,1) NOT NULL,
[Rec_NM] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rec_TM] [datetime] NULL,
CONSTRAINT [PK__tbl_RecordNum__1A9EF37A] PRIMARY KEY CLUSTERED
(
[Rec_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[tbl_RecordNum] ON
INSERT [dbo].[tbl_RecordNum] ([Rec_ID], [Rec_NM], [Rec_TM]) VALUES (1, NULL, NULL)
SET IDENTITY_INSERT [dbo].[tbl_RecordNum] OFF

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-01 : 16:19:38

yes that is possible.
vars like i or rec_count must be declared and var names need always a @ in front


declare @i int
declare @rec_count int

now you need to set @rec_count to the wanted value and @i to the startvalue

set @rec_count = 10
set @=1

then you can do the loop


while @i < @rec_count
begin
 insert tbl_RecordNum
 select @i,null,null
 set @i=@i+1
end

But now I see that your Rec_Id is an identity, so you have only to insert the nullvalues...

while @i < @rec_count
begin
 insert tbl_RecordNum
 select null,null
 set @i=@i+1
end

No, you're never too old to Yak'n'Roll if you're too young to die.

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-02-01 : 16:35:43

Webfred,
Thanks for this info.
I am getting this error.
Must declare the scalar variable "@".

I am decaring the variables after creating the table.
Should i declare them earlier in the code?

declare @i int
declare @rec_count int
set @rec_count = 10
set @=1
SET IDENTITY_INSERT [dbo].[totalBFT] ON
while @i < @rec_count
BEGIN
INSERT [dbo].[totalBFT] select null,null
set @i=@i+1
SET IDENTITY_INSERT [dbo].[totalBFT] OFF
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-01 : 16:38:00

my bad!
set @=1 is a typo. it should be set @i=1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-01 : 16:39:37

and with identity_insert on you should use my first approach (inserting the @i,null,null), you know?

No, you're never too old to Yak'n'Roll if you're too young to die.

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-02-01 : 16:48:52

Hi Webfred thanks,

It failed when i had @i=1 in the code. It failed a couple of times .but now, it seems to work. No clue as to what i did different.

After it worked i deleted the table again and mad this change moved the identity insert OFF after the END

declare @i int
declare @rec_count int
set @rec_count = 10
set @i=1
SET IDENTITY_INSERT [dbo].[totalBFT] ON
while @i < @rec_count
BEGIN
INSERT [dbo].[totalBFT] select null,null
set @i = @i+1
END
SET IDENTITY_INSERT [dbo].[totalBFT] OFF

Thanks a Lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources