"nick" <nick@discussions.microsoft.com> wrote in message
news:CA8CF591-5162-49EF-8E87-ACF291628CBE@microsoft.com...
> Yes, transaction batch is the different. However, how SSIS comparing with
> bulk insert? Anyway to implement a kind of bulk insert using TSQL that
> read
> ACCESS MDB files (via openrowset?)...
>
> "Jéjé" wrote:
>
>> there is a big difference !!!
>> SSIS use a bulk insert command which is far better then a simple insert
>> command.
>>
>> you have to setup correctly the commit batch size.
>> for example, if you have 1 million of rows, setup the batch size to 10
>> 000
>> insert 10 000 rows, commit this batch, then load the next 10 000 rows....
>> instead-o creating 1 transaction for the million of rows, multiple
>> smaller
>> transaction are created.
>> this reduce the time taken to load the data, reduce the log file size
>> needed.
>>
>> so the answer is clear: use SSIS.
>>
>> "nick" <nick@discussions.microsoft.com> wrote in message
>> news:2705757C-0AE0-4830-969B-46EAD54A0BA7@microsoft.com...
>> >I need to dump Access MDB files to SQL Server 2005. No extract and
>> >transfer,
>> > just transfer the raw data. However, the mdb files can be reaching the
>> > limitation of 2G.
>> >
>> > Is there any benifit (especially performance) to use SSIS to load the
>> > MDB
>> > files? Or a simple SQL statement (with openquery to open the oledb Jet
>> > database, 'insert into '+@tablename + ' select * from openquery(....))
>> > will
>> > be the same?
>>
>>
>>