COMMIT TRAN - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

COMMIT TRAN

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-09-17 : 05:48:24

Hi

we have 6 crores records. we have to insert into another table.
so i want to commit every 1 lacs records...
how to achieve this in T-sqL

Please help me out

thanks

smarty
Starting Member

13 Posts

Posted - 2009-09-17 : 06:06:03

BEGIN TRY
BEGIN TRANSACTION;

--This is where you copy your records

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH;

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 06:08:00

translated:
we have 6 million records. we have to insert into another table.
so i want to commit every 100,000 records...
how to achieve this in T-sqL

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

smarty
Starting Member

13 Posts

Posted - 2009-09-17 : 06:13:47

quote:
Originally posted by webfred

translated:
we have 6 million records. we have to insert into another table.
so i want to commit every 100,000 records...
how to achieve this in T-sqL

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

Thanks for the translation

WoodHouse ignore my post

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-17 : 06:31:28

quote:
Originally posted by smarty

BEGIN TRY
BEGIN TRANSACTION;

--This is where you copy your records

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH;

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

But how does it commit after each 100000 records ?

smarty
Starting Member

13 Posts

Posted - 2009-09-17 : 06:38:17

quote:
Originally posted by sanoj_av

quote:
Originally posted by smarty

BEGIN TRY
BEGIN TRANSACTION;

--This is where you copy your records

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH;

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

But how does it commit after each 100000 records ?

Check my second post

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-17 : 07:47:31

/*
Woodhouse,
You can achieve the functionality in the following way.But I am not sure about the permormance implications when applied against a 6 million records table as the where clause is based on a non persistant column. I think you are trying to commit it for every 100000 records due to some log file size issues.

*/

Declare
@Processed Bigint,
@cnt Bigint

Set @Processed=0
Select @cnt=Count(1) from sys.columns

Begin Try
create Table #tmp(name Varchar(100))
While(@Processed<=@cnt)
Begin
Begin Tran
Insert into #tmp
Select top 100 name from
(Select row_number() over(order by name) as SrNo ,name from sys.columns) A
where
SrNo > @Processed
order by
SrNo
Set @Processed=@Processed+100
Commit Tran
End
End TRy

Begin Catch
RollBack Tran
End Catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources