NOTE 310 Part I EXAM PREPARATION So far,
NOTE 310 Part I EXAM PREPARATION So far, all the examples have involved exporting data from SQL Server. Now it s time to take a look at importing data. The BCP command works the same both ways: you should specify IN instead of OUT to import data into SQL Server. For large files, with more than a couple thousand rows perhaps, you should turn on the Select Into/Bulkcopy option for the database, or set the database recovery mode to BULK_LOGGED or SIMPLE. These options disable all transaction log backups while they are turned on, and you must do a full backup to get transaction log backups to work afterwards. What the option does is for certain operations, namely those involving SELECT INTO and BULK COPY; it changes how transaction logging works. Typically, whenever you insert a row, SQL Server logs the row being What About SELECT INTO/BULKCOPY? inserted into the transaction log. This prevents data loss in case of If you re used to using SQL Server 7.0 power outage and enables you to do point-in-time database recovery. or previous versions, you re probably This also significantly slows down the process of inserting huge wondering what happened to the numbers of records. Switching to BULK_LOGGED or SIMPLE changes the SELECT INTO/BULKCOPY option. It s behavior so that rather than logging the entire row insert, SQL been replaced by a Recover Mode Server just logs the page allocations, which involves a lot less over- option. You can choose one of three head. Basically, when you do a BCP and the database is set for recovery modes: FULL, BULK_LOGGED, BULK_LOGGED or SIMPLE recovery, all the data goes into allocated space or SIMPLE. FULL mode is the default for everything except Desktop and the in the database; and when the copy commits, it attaches the allo- Data Engine versions of SQL Server. cated space to the table. It s really fast, and it s still very safe because BULK_LOGGED is similar to the old all the page allocations are logged, and if the transaction fails and SELECT INTO/BULKCOPY option, in that has to roll back, the pages are deallocated. This process is called Fast any bulk row operations have only Bulk Copy. allocations logged, not the data. FULL In addition to having the BULK LOGGED or SIMPLE recovery option mode is the normal mode for most operations; it offers the widest variety selected, you need to do a few other things to get fast bulk copy of recovery options. to work. The target table can t be involved in replication. The target table can t have any triggers. The target table either has zero rows or has no indexes. The TABLOCK hint is specified. This is covered in more detail later in the section; for now, the TABLOCK hint is another parameter you can give BCP to make it acquire a table lock before it begins writing data.
For high quality java hosting services please check tomcat web hosting website.