August 5, 2008 at 6:49 pm
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
August 5, 2008 at 10:04 pm
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
Change is inevitable... Change for the better is not.
August 5, 2008 at 10:11 pm
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
August 5, 2008 at 11:12 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply