Partial Commits for large datasets

#1

I am working on large datasets, and would like to set commit statements after regular intervals, instead on one huge commit at the end. This commit should depend on the number of rows. For example if my total table size is about 500000 rows, I should be able to set my commit at 10000 rows.
Is there a way to do this in DTS
Thanks

#2

Yep

Last tab of the DataPump setup.

Fetch and commit sizes

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I am working on large datasets, and would like to set commit statements
after regular intervals, instead on one huge commit at the end. This commit

should depend on the number of rows. For example if my total table size is
about 500000 rows, I should be able to set my commit at 10000 rows.

Quote:

Is there a way to do this in DTS?
Thanks

#3

Allan
Are you refering to the "Fetch" in Data movement properties.
I was not able to fing a "COMMIT" property. The only one close to this was the "insert batch size" in the SQL server properties Area. I will not be able to use this since this is SQL server specific. I want a solution which can be across Oracle also
Thanks

#4

Sorry yes that is what I meant.

The InsertCommitSize property is dependent on the FastLoad option being set.
The FastLoad option pertains to a SQLOLEDB dataSource so I also think you
won't be able to use this.

Are there any extended properties on the Oracle driver you can set ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Allan,
Are you refering to the "Fetch" in Data movement properties.
I was not able to fing a "COMMIT" property. The only one close to this was
the "insert batch size" in the SQL server properties Area. I will not be

able to use this since this is SQL server specific. I want a solution which
can be across Oracle also.

Quote:

Thanks.

#5

Hi there,
I have experienced an issue that might be similar and the solution applied may be useful.
I had to delete six months (180.000.000) records from a table but had no log space to delete it by month, week or even daily.
I made a stored procedure that opens a cursor of 20.000 records, delete them and CHECKPOINT the operation, start it over.
Left the job running overnight and it worked. Not elegant, but the job is done.
Hope this helps

Valmir Meneses
MCDBA, MCAD.NET

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Partial Commits for large datasets

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Partial Commits for large datasets - 01-05-2004 , 07:21 AM

Re: Partial Commits for large datasets - 01-05-2004 , 07:26 AM

Re: Partial Commits for large datasets - 01-06-2004 , 03:26 AM

Re: Partial Commits for large datasets - 01-06-2004 , 03:51 AM

Re: Partial Commits for large datasets - 01-06-2004 , 05:21 AM