I need to pull a table from one server into another for testing purposes. I
have a linked server setup from server A to server B and I run a query like
this:

select col1,col2,col3 into TestTable from server.database.dbo.TestTable

There are several million rows in the table and this takes about 45min to an
hour to run.

If instead I setup an SSIS package to transfer the data and use the bulk
insert mode it transfers the data in 7-10 minutes.

Since the database is in simple mode the select into statement should be
non-logged and so I don't see why there would be such a large discrepancy in
the load times.

GPage,

Even though the recovery model is set to simple, row inserts still do a lot
of logging and updating. There is no "non-logged" state of a database,
simply somewhat reduced logging and quicker discarding of the transaction
log contents.

BULK INSERT actually uses another, much faster, approach to inserting data
and will always be faster for a sizeable amount of data than SELECT INTO.
It also does some logging, though.

So, the difference in timings is not surprising.

RLF


"GPage" <GPage (AT) discussions (DOT) microsoft.com> wrote