Shared Flashcard Set

Details

DBA, Chapter 7
importing and exporting data
30
Computer Science
Undergraduate 4
03/20/2011

Additional Computer Science Flashcards

 


 

Cards

Term
True or False, when you need to import or export large amounts of data, using an insert statement for each row is efficient
Definition
false, using an Insert statement for each row is not very efficient
Term
what program is the most efficient way to import well-defined data in files into SQL Server or to export tables to a file
Definition
Bulk Copy Program (BCP.exe)
Term
when you need to perform transformations or error-handling routines during import/export, what's an alternative to BULK INSERT
Definition
SQL Server Integration Services (SSIS)
Term
____ is a utility that is executed from the command line, using switches
Definition
BCP
Term
bulk copy switch, -c
Definition
Character data mode (ASCII text)
Term
bulk copy switch, -C
Definition
codepage used by the import file
Term
bulk copy switch, -e enfile
Definition
stores error messages in the specified error file
Term
bulk copy switch, -f formatfile
Definition
sets the name and path of a BCP format file
Term
bulk copy switch, -N
Definition
Sets native export for a noncharacter data and Unicode character export for character data
Term
bulk copy switch, -P password
Definition
Password to use for login
Term
bulk copy switch, -T
Definition
Uses a trusted connection
Term
-U username
Definition
sets the user name for login
Term
-w
Definition
sets wide character (Unicode) mode
Term
you can start an interactive session with BCP, or you can set the necessary responses in a __________
Definition
format file
Term
at the end of the BCP session, you are asked to save your responses in a file called a ________
Definition
format file
Term
what switch can you use to use the format file in another session
Definition
-f switch
Term
when exporting data using BCP, how can you specify the tab as a terminator?
Definition
\t
Term
when exporting data using BCP, how can you specify the carriage return as a terminator?
Definition
\r
Term
when exporting data using BCP, how can you specify the newline as a terminator?
Definition
\n
Term
when importing with the BCP, what three things are disabled by default unless you use the -h switch?
Definition
triggers, CHECK and FOREIGN KEY constraints
Term
during BCP, what three constraints are always enforced?
Definition
UNIQUE, PRIMARY KEY, and NOT NULL constraints
Term
what is a T-SQL command for importing data into a database?
Definition
BULK INSERT
Term
how is BULK INSERT different from BCP?
Definition
1. you cannot export data
2. you do not need to specify the instance name or login credentials
Term
true or false, BULK INSERT is faster than BCP
Definition
true
Term
true or false, BULK INSERT works from within the SQL Server instance
Definition
True
Term
what DB recovery model should you set before using BULK INSERT?
Definition
Bulk-Logged
Term
before BULK INSERT, what kind of lock should you set on the bulk load value?
Definition
table lock
Term
what provides a subset of the SSIS capabilities within SSMS?
Definition
SQL Server Import & Export Wizard
Term
how do you start the SQL Server Import & Export Wizard?
Definition
Right-click a database in Object Explorer, select Tasks, then Import Data or Export Data
Term
what are the differences between the Import & Export Wizard and BCP/BULK INSERT
Definition
1. Can use any data source recognised by SSIS, including MS Office or XML files
2, Supports any data source or destination accessible through an OLD DB provider
3. Does no require a SQL Server instance as a source or target
4. Can move data from multiple tables or files in a single operation.
Supporting users have an ad free experience!