asked on

how to use for loop or loop the insert statement in Sql Server 2000

Hi Experts,

I have a stroed procedure lr which have an insert statement as below:

ALTER Procedure lr

as
my update statement.....

Declare @pc int
Select @pc = MAX(pc)From test.dbo.localtest
Declare @pn nvarchar(50)

Select @pn= pn from test2.dbo.remotetest a where pn NOT IN (Select pn from test.dbo.localtest)

begin
Insert into test.dbo.localtest(pc, pn) VALUES
(@pc+ 1,@pn)

END

but the above statement is inserting only one record at a time and next statement only after executing the Stored procedure 2nd time and vice versa

I want to keep the insert statement between loop but not getting logic how to loop insert statement.

Please check my above statement and provide me insert statement with loop code in which at a time all the records should be inserted

ALTER Procedure lr
 
as
my update statement.....
 
Declare @pc int 
Select @pc = MAX(pc)From test.dbo.localtest
Declare @pn nvarchar(50) 
 
Select @pn= pn from test2.dbo.remotetest a where pn NOT IN (Select pn from test.dbo.localtest)
 
begin
Insert into test.dbo.localtest(pc, pn) VALUES
(@pc+ 1,@pn)
 
END

Open in new window

Brendt Hess

There are two common ways to do this, and one uncommon way. Since I prefer the uncommon, allow me to demonstrate. This version does not require a loop to perform the task you are attempting to perform.

DECLARE @t table (pc int identity(1,1), pn nvarchar(50))
 
INSERT INTO @t (pn)
SELECT a.pn
FROM test2.dbo.remotetest a 
LEFT JOIN test.dbo.localtest b
    ON a.pn = b.pn
WHERE b.pn is null
 
Declare @pc int 
Select @pc = MAX(pc)From test.dbo.localtest
 
Insert into test.dbo.localtest(pc, pn)
SELECT @pc + pc, pn
FROM @t

Open in new window

appari

try this

ALTER Procedure lr

as
my update statement.....

Declare @pc int
Select @pc = MAX(pc)From test.dbo.localtest
Declare @pn nvarchar(50)

Select @pn= pn from test2.dbo.remotetest a where pn NOT IN (Select pn from test.dbo.localtest)
while @pn is not null
begin
Insert into test.dbo.localtest(pc, pn) VALUES
(@pc+ 1,@pn)

Select @pn= pn from test2.dbo.remotetest a where pn NOT IN (Select pn from test.dbo.localtest)
Select @pc = @pc+ 1

END

muneerz

ASKER

Thanks a lot Expert :) for helping me in loop,

The below code is inserting all the records of other table but more than thousand times.

Please provide me the loop in which the loop should update all records at a time but not multiple times,

i mean if pn = 2 then in localtest table it is updating 2 more than 1000 times from remote test

ALTER Procedure lr

as

if( EXISTS( Select * from test2.dbo.remotetest AS a, test.dbo.localtest b where a.pn=b.pn))

UPDATE test2.dbo.remotetest
SET pc = B.pc FROM test2.dbo.remotetest A,test.dbo.localtest B
WHERE A.pn= B.pn

ELSE

Declare @pc int
Select @pc = MAX(pc)From test.dbo.localtest
Declare @pnnvarchar(50)
while @pc is not null
begin

Select @pn= pnfrom test2.dbo.remotetest a where pnNOT IN(Select pnfrom test.dbo.localtest)

Insert into test.dbo.localtest(pc, pn) VALUES
(@pc + 1,@pn)

END

muneerz

ASKER

help me

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

muneerz

ASKER

Thank YOu