Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
to bcp or not to bcp 1
- Thread starter andreis
- Start date
tlbroadbent
MIS
If you are going to change the structure of the existing DB, BCP would probably be best for exporting the data. You could also use DTS.
Once you modify the DB, you could import the data using BCP, DTS or BULK INSERT. All do bulk copies and would be reasonably fast.
Another option is to restore a backup of the database to a different name. Then you can transfer the data to the modifed DB using T-SQL. I find this is usually easier but may take longer to run. Terry
Once you modify the DB, you could import the data using BCP, DTS or BULK INSERT. All do bulk copies and would be reasonably fast.
Another option is to restore a backup of the database to a different name. Then you can transfer the data to the modifed DB using T-SQL. I find this is usually easier but may take longer to run. Terry
- Thread starter
- #3
Terry, thanks.
I'd like to expand a little my question, if I may:
Is bcp good to trasfer data if the structure of the tables changed?
Also, it looks like I can't use DTS because after I use Export Wizard on SQL Server 7 to transfer 500 MB of data, my transaction log on target DB grows to 380 MB (select into\bulk copy option is checked). And I can't shrink it. So, the question is how to avoid transaction log expanding during data import\export? I can't break data onto smaller pieces, because there are only few tables, and transferring data from each of them provokes log expanding.
Andrei
I'd like to expand a little my question, if I may:
Is bcp good to trasfer data if the structure of the tables changed?
Also, it looks like I can't use DTS because after I use Export Wizard on SQL Server 7 to transfer 500 MB of data, my transaction log on target DB grows to 380 MB (select into\bulk copy option is checked). And I can't shrink it. So, the question is how to avoid transaction log expanding during data import\export? I can't break data onto smaller pieces, because there are only few tables, and transferring data from each of them provokes log expanding.
Andrei
-
1
- #4
Hello andreis,
In SQL 7.0 it is possible to change a table of structure without the use of BCP command and still keep all the data. There are some limitations on it, but when you change a table to add columns or to delete columns or to make one column bigger SQL*Server will do it automatically for you.
You only have to change the column in design time in the Enterprise manager and SQL*Server will generate the nessesary scripts.
Hope this helps,
JNC73.
In SQL 7.0 it is possible to change a table of structure without the use of BCP command and still keep all the data. There are some limitations on it, but when you change a table to add columns or to delete columns or to make one column bigger SQL*Server will do it automatically for you.
You only have to change the column in design time in the Enterprise manager and SQL*Server will generate the nessesary scripts.
Hope this helps,
JNC73.
- Thread starter
- #5
- Status
Similar threads
- Locked
- Question
- Locked
- Question