Loop insertion

Question

Loop insertion

Ayie

SSC Eights!

Points: 828
More actions
August 5, 2008 at 6:49 pm

#190775

Anyone can help me to enhance this script for insert of data 1 by 1 from another table, this is the same data inside, same index, fields, etc. I want to insert 1 by 1 into new table, that not cause of blocking.
begin transaction
declare @3mthsAgo datetime
set @3mthsAgo = convert(datetime, convert(varchar, getdate()-90, 112), 103)
while exists (select * FROM dbo.Dummy_INT_HL7INMESGTRANS with (nolock))
begin
set rowcount 1
set IDENTITY_INSERT dbo.Dummy_INT_HL7INMESGTRANS ON
insert into dbo.Dummy_INT_HL7INMESGTRANS (msg_id,message,status,received_date,process_date,
institution,app_code,int_hl7inmesgtrans_id)
select msg_id,message,status,received_date,process_date,
institution,app_code,int_hl7inmesgtrans_id FROM dbo.Dummy_Data_INT_HL7INMESGTRANS
set rowcount 0
end
SET IDENTITY_INSERT dbo.Dummy_INT_HL7INMESGTRANS OFF
commit transaction
thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

How many rows are you talking about copying? I'm thinking that you don't actually need the WHILE loop because of the explicit transaction you've declared. Use set based code based on INSERT/SELECT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ayie SSC Eights! Points: 828 More actions · Answer 2

only 100 records, I want to trace some blocking error while im deleting, at the same time insert 1 record at a time. this one is running but some problem, the records is not inserted into table... can you help me to enhance this code

thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

The WHILE condition doesn't make sense for the code you posted... it will run forever because something will always exist in the table.

Also, you're trying to insert into the same table you're selecting form.

Keep in mind that anything that changes data will cause blocking because the affected rows must lock to be changed. Most blocking is a healthy part of SQL Server transactions.

Last but not least, you aren't using the 3 month variable at all to control your archive process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)