Transfer Data From One Database to another Database

14 Sep 2006 11:18 AM

Lalit

Hi Friends,

I have to transfer data from one database to another and some of tabels in
both the databases are different in no. of columns and some new tables are
added. Some tables are having 1,000,000 records.

I have written INSERT INTO SELECT queries. for the tables having 1,000,000
records , have inserted 1000 rows in a transation but i am getting the
timeout error.

How can i do this? If there is another way to do this then pls tell me that
also.
I have tried DTS also but in that customized queries with some constant
data is required.

With Thanks and regards,
Lalit

14 Sep 2006 11:32 AM

Uri Dimant

Lalit

1) Try BULK out to the file the data especially of the big tables and then
use BULK INSERT/BCP to insert the data

2) SELECT * INTO may work under some circumstances faster than INSERT INTO
Table

3) Try script out of indexes on the target database's table and recreate
them back just after insertion

4) Do that when you server is under low activities

Show quote

"Lalit" <La***@discussions.microsoft.com> wrote in message
news:8A5BEDF3-E8FB-4D93-AF24-D5D42FB9A69C@microsoft.com...
> Hi Friends,
>
> I have to transfer data from one database to another and some of tabels in
> both the databases are different in no. of columns and some new tables are
> added. Some tables are having 1,000,000 records.
>
> I have written INSERT INTO SELECT queries. for the tables having
> 1,000,000
> records , have inserted 1000 rows in a transation but i am getting the
> timeout error.
>
> How can i do this? If there is another way to do this then pls tell me
> that
> also.
> I have tried DTS also but in that customized queries with some constant
> data is required.
>
> With Thanks and regards,
> Lalit