Chapter 16
Importing and Exporting Data




by Orryn Sledge

You can use various methods to import and export SQL Server data. Almost all systems require some type of data transfer. BCP is the utility provided by SQL Server to transfer data. This chapter discusses in detail the BCP utility and also covers alternatives to BCP.

BCP

BCP stands for Bulk Copy Program. It is the only tool SQL Server provides (with the exception of a few tricks discussed later in this chapter) to import and export data. Data can be either native mode (SQL Server specific) or character mode (ASCII text). ASCII text data is commonly used to share data between SQL Server and other systems (see Figure 16.1).

Figure 16.1.
Common uses of BCP.


Is It Love or Hate?
As a DBA, you will probably have a love/hate relationship with BCP. BCP is limited in scope and lacks common file formats, but it does provide excellent performance. For those new to SQL Server, the following list provides some insight into BCP. These are the reasons why I like BCP:

Performance
: BCP is one the fastest raw data loaders around. I have seen BCP turn in impressive performance numbers compared to other import/export products.

Minimal overhead
: Because BCP is command-line based, it requires a nominal amount of memory to run compared to today's memory-intensive GUI applications. This leaves memory for other tasks. Now for the drawbacks of BCP. These are the reasons why I hate BCP:

Unforgiving syntax
: BCP's switches are case- and order sensitive. This is because BCP's origins stem back to Sybase and the UNIX world, where commands are case sensitive. I wish that Microsoft would break with tradition and implement a user-friendly version of BCP.

Minimal file support
: Basically, the choices are ASCII text, native SQL Server format, or nothing at all. Do not try to load an Excel spreadsheet or an Access database directly into SQL Server; it will never work. To load non-SQL Server data, you must export the data as ASCII text and then import the text file into SQL Server. It would be nice if BCP could directly import/export today's popular file formats such as Access, Excel, dBASE, and so on.

Inadequate error messages
: BCP's error messages are minimal and too generic. I wish that Microsoft would enhance BCP's error messages to be more informative and specific. As you can see, BCP is far from perfect, but it is the only utility provided by SQL Server to import/export data. Oh well! With this in mind, the remainder of this chapter will provide you with some useful tips and tricks to make your life easier when you use BCP.

BCP Syntax

Use the following syntax to perform BCP operations:

bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]


NOTE: With BCP, you can use - or / to preface a switch. For example, the following two statements are equivalent:

bcp pubs..sales out sales.out /c /Usa /P
bcp pubs..sales out sales.out -c -Usa -P

Parameter Explanation
database_name Name of the database being accessed.The database name is optional; if the database name is omitted, the user's default database is used (optional).
owner Owner of the table or view being accessed (optional).
Tip:
Use the .. symbol to specify ownership. The .. syntax is more generic than specifying an owner (for example: pubs..authors instead of pubs.dbo.authors).
table_name Name of the table or view being accessed (required).
Tip:
Use the # or ## symbol to copy a temporary table.
in | out Direction of data transfer, where in means import and out means export (required).
datafile The name of the data file for an import or the name of the file to be created during an export. A path can be included with this statement, such as c:\mssql\binn\authors.txt (required).
/m maxerrors Maximum number of errors that can occur before the BCP operation is terminated. Each failed insert counts as one error. Default value is 10 (optional).
/f formatfile The name of the format file used to import or export data. A path can be included with this statement, such as c:\mssql\binn\authors.fmt (optional).
/e errfile The name of the error file to store BCP error messages and unsuccessfully transferred rows. A path can be included with this statement, such as c:\mssql\binn\authors.err (optional).

Tip:
Error files are useful for pinpointing BCP errors during unattended operations such as nightly data imports.
/F firstrow The number of the first row to copy (optional).
/L lastrow The number of the last row to copy (optional).
Tip:
The /F and /L switches are useful when copying portions of data. For example, to export the first 1,000 records from a table, use the following syntax: /F 1 /L 1000
/b batchsize The number of rows transferred in a batch. The default setting is the number of rows in the data file (optional).
/n Native data mode. Native data is SQL Server specific. Native data mode does not prompt the user for field information (optional).
/c Character data mode. Character data (ASCII) can be transferred to and from SQL Server tables and other non-SQL Server products. Character mode does not prompt the user for field information. By default, fields are tab delimited and rows are newline delimited (optional).
Tip:
Character data mode is usually easier to work with than native data mode.
/E New with version 6.x. Used when importing data into a table that contains an identity datatype and you want to populate the column with values from the data file. If this switch is omitted, SQL Server automatically populates the identity column and ignores the field's corresponding data values in the import file (optional). The following example shows how the /E switch impacts data imports:

Sample table structure:
id int identity(1,1)
descr char(15)
Sample data file:
5 xxx
6 yyy
7 zzz
BCP syntax WITHOUT the /E switch:
bcp sales..table2 in
table2.txt /c /U sa /P
Results:
id descr
---------- -----------------------------------
1 xxx
2 yyy
3 zzz


Notice the values in the id column. SQL Server populated the id column with an automatically incremented data value. It ignored the values 5,6,7 in the data file.

The following is BCP syntax with the /E switch:

bcp sales..table2 in
table2.txt /c /E /U sa /P
Results:
id descr
----------- -------------
5 xxx
6 yyy
7 zzz


With the /E switch, the values in the text file were observed and SQL Server did not automatically generate a set of data values for the id column.

Tip: Use the /E switch to preserve data values when you are unloading and reloading data in a table that contains an identity datatype. Otherwise, SQL Server automatically populates the identity column with its own set of values.
/t field_term Field terminator (optional). See Table 16.1 for BCP terminators.
/r row_term Row terminator (optional). See Table 16.1 for BCP terminators.
/i inputfile File to redirect input. This switch is not generally used (optional).
/o outputfile File to redirect BCP output (optional).
Tip:
Use the /o switch to log BCP output during unattended BCP operation. This creates a useful trail of BCP output that can be used to monitor and diagnose BCP performance and execution.
/U login_id SQL Server login ID (required).
/P password SQL Server password. If the password is omitted, BCP prompts you for a password (required).
Note:
If you are using integrated security or your SQL Server login does not have a password, BCP still prompts you for a password. To bypass BCP's prompt, use the /P switch without a password, as in the following example: BCP pubs..authors in authors.txt /U sa /P
/S servername The name of the server that contains the database and table you are working with. The /S servername switch is required if you are using BCP from a remote client on a network (optional).
/v Displays the version of DB Library in use (optional).
Note:
If you are concurrently running SQL Server version 6.x and version 4.2x, be certain that you are using the correct version of BCP. To determine which version of BCP is in use, type BCP /v. Version 6.x's copyright date will be greater than or equal to 1995.
/a packet_size The number of bytes contained in a network packet. The default value for Windows NT Server and Windows NT clients is 4096 ; the default value for MS-DOS clients is 512. Valid sizes are 512 to 65535 bytes (optional).
Tip:
Depending own your network architecture, you may be able to improve BCP performance by increasing the packet size. Try setting the packet size between 4096 and 8192 bytes. Use the statistics returned by BCP (the clock time and rows per second) to help tailor this setting.
Table 16.1. Valid BCP terminators.
Terminator Type Syntax
tab \t
new line \n
carriage return \r
backslash \\
NULL terminator \0
user-defined terminator character (^, %, *, and so on)

Permissions Required to Run BCP

No permissions are required to run the BCP command-line utility. However, to use BCP to copy data into a table, the user must be granted INSERT permission to the target table. To export data from a table, the user must be granted SELECT permission for the source table.

Character Mode versus Native Mode

BCP can import/export data in character file format or native file format. Character mode is plain old ASCII text. Use the /c switch or a format file to specify character mode. Native mode uses special formatting characters internal to SQL Server to represent data. Use native mode only when you are transferring data between SQL Server tables. Use the /n switch to specify native mode. Following is sample output from character mode BCP:

bcp pubs..jobs out jobs.txt /c /U sa /P
1 New Hire - Job not specified 10 10
2 Chief Executive Officer 200 250
3 Business Operations Manager 175 225


TIP: Character mode is usually easier to work with than native mode because you can view the contents of a character mode data file with a standard text editor.


Interactive BCP

Interactive BCP is used to selectively import or export data. Interactive mode is automatically activated when the following switches are not included in the BCP statement:

/n
(native format)
/c (character format)
/f (format file)

Through the use of interactive prompts, you can tailor BCP to your import and export specifications. Interactive BCP prompts you for four pieces of information:

The following are sample interactive BCP prompts:

Enter the file storage type of field discounttype [char]:
Enter prefix-length of field discounttype [0]:
Enter length of field discounttype [40]:
Enter field terminator [none]:


TIP: When importing data, you can skip a column by entering 0 for prefix length, 0 for length, and no terminator. You cannot skip a column when exporting data.

At the end of an interactive BCP session, you receive the following prompt:

Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]:

If you answer yes at this prompt, your interactive responses are saved to a format file. This enables you to specify at a later time the /f switch (format file) to automatically reuse the information from your interactive BCP session.

File Storage Type

The file storage type specifies the datatypes used to read from and write to data files. Table 16.2 lists valid file storage types.


TIP: When working with ASCII files, set all file storage types to char, regardless of the table's datatypes. This is the only way you can load ASCII data into SQL Server using BCP.

Prefix Length

SQL Server uses the prefix length to store compacted data. When working in native mode, accept the default values whenever possible.


TIP: When working with fixed-width ASCII data, set the prefix length to 0.

Field Length

The field length specifies the number of bytes required to store a SQL Server datatype. Use default field lengths whenever possible, otherwise data truncation or overflow errors may occur. Table 16.3 lists default field lengths.


TIP: When importing and exporting ASCII fixed-width data files, you may need to modify the field length to match your import/export specification. For example, to export a char(15) column as a 25-byte piece of data, specify a field length of 25. This pads the data length to 25 bytes.

Field Terminator

The field terminator prompt controls how field data is delimited (separated). The default delimiter is no terminator. See Table 16.4 for valid field terminators.


TIP: The last field in a table acts as a row terminator. To separate rows with a newline delimiter, specify \n at the field terminator prompt.


NOTE: At the BCP command line, you can also use the /t (field terminator) and /r (row terminator) switches to specify terminators.

Table 16.4. Valid field terminators.
Terminator Type Syntax
tab \t
new line \n
carriage return \r
backslash \\
NULL terminator \0
user-defined terminator character (^, %, *, and so on)

Format Files

A format file is a template for BCP to use when you import or export data. With this template, you can define how BCP should transfer your data.

The easiest way to create a format file is to initiate an interactive BCP session. Interactive mode is initiated when you do not specify one of the following switches:

At the end of your interactive session, you see the following prompt:

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]:sample.fmt

At this prompt, enter a filename to save the format information. SQL Server then creates a format file, which is really just an ASCII text file (see Figure 16.2). You can make modifications to an existing format file by using a standard text editor.

Figure 16.2.
A sample format file.


TIP: Use the FMT extension when saving format files to simplify file identification.

Once you have saved the format file, you can reuse the format file by specifying the /f (format file) switch, as in the following example:

bcp sales..discounts in discount.txt /f sample.fmt /U sa /P


NOTE: If a table has a corresponding format file, any column modification to the table must be reflected in the format file. For example, if you drop a column from a table, you must also remove the column from the format file.

Sample BCP Scripts

This section describes how to use BCP to perform typical import and export routines. The examples discussed in this section use the pubs..discounts table.

The following is the structure of the discounts table:

discounttype varchar (40)
stor_id varchar
lowqty smallint
highqty smallint
discount decimal(0, 0)

The following is discounts table data:

discounttype stor_id lowqty highqty discount
---------------------------------------- ------- ------ ------- --------
Initial Customer (null) (null) (null) 10.50
Volume Discount (null) 100 1000 6.70
Customer Discount 8042 (null) (null) 5.00

Simple Import

This example uses the /c switch to load a data file that contains tab-delimited fields and newline-delimited rows. For this example, the import data is contained in a file named disc.txt. Following are the contents of the sample import file:

Preferred Customer 6380 200 800 5.5
Valued Customer 7896 100 1000 8.5

The following syntax shows how to import the contents of the disc.txt file into the discounts table:

bcp pubs..discounts in disc.txt /c /U sa /P

Simple Export

This example uses the /c switch to export data to a file with tab-delimited fields and newline-delimited rows. The following syntax shows how to export the contents of the discounts table to the discount.out file:

bcp pubs..discounts out discount.out /c /U sa /P

Following is the output:

Initial Customer 10.50
Volume Discount 100 1000 6.70
Customer Discount 8042 5.00

Comma-Delimited Import

This example imports a data file that contains comma-delimited fields and newline-delimited rows. The /t switch specifies a comma delimiter; the /r\n switch specifies a newline row delimiter. For this example, the import data is contained in a file named disc2.txt. Following are the contents of the sample import file:

Preferred Customer,6380,200,800,5.5
Valued Customer,7896,100,1000,8.5

The following syntax shows how to import the contents of the disc2.txt file into the discounts table:

bcp pubs..discounts in disc2.txt /c /t, /r\n /U sa /P

Comma-Delimited Export

This example exports the discounts table to a file with comma-delimited fields and newline row delimiters. The following syntax shows how to export the contents of the discounts table to the disc3.txt file:

bcp pubs..discounts out disc3.txt /c /t, /r\n /U sa /P

Following is the output:

Initial Customer,,,,10.50
Volume Discount,,100,1000,6.70
Customer Discount,8042,,,5.00

Fixed-Length Import

This example uses a fixed-length ASCII text file named disc4.txt. Table 16.5 shows the layout of the text file.
Table 16.5. The file layout of disc4.txt.
Column Name File Length File Position
discounttype 40 1-39
stor_id 4 40-43
lowqty 6 44-49
highqty 6 50-55
discount 5 56-60


The following is sample data from disc4.txt:

123456789012345678901234567890123456789012345678901234567890
Preferred Customer 6380200 800 5.5
Valued Customer 7896100 1000 8.5

For fixed-length data transfers, SQL Server needs to know the field positions in the data file. An easy way to do this is to use interactive BCP. To begin interactive BCP, use the following command:

bcp sales..discounts in disc4.txt /U sa /P

For the first two prompts, you can accept the default values because they match the layout in the data file. For the third, forth, and fifth prompts (see the highlighted text in Figure 16.3), you have to override the default prompts.


NOTE: When importing fixed-length ASCII data, always use char for the file storage type and 0 for the prefix length.

Fixed-Length Export

Suppose that you need to export the discounts table in a fixed-length file format and the format must follow the specification used in the previous example. No problem; you can reuse the format file you created in the previous example (see Figure 16.4).

The following syntax shows how to export the contents of the discounts table to the disc4.out file:

bcp pubs..discounts out disc4.out /c /f disc4.fmt /U sa /P

Figure 16.3.
Interactive BCP responses.


Figure 16.4.
The
disc4.fmt format file.

Skipped Fields on Import

Suppose that you want to skip the columns stor_id, lowqty, and highqty when you load the disc4.txt ASCII file. To do this, you must modify the format file. To skip a column, enter 0 for the table column order (see Figure 16.5).

Figure 16.5.
The format file used to skip columns.


After you modify your format file, you can use the following BCP syntax to load the data:

bcp pubs..discounts in disc4.txt /c /f disc4.fmt /U sa /P

Skipped Fields on Export

BCP does not allow you to skip a column in a table during an export. However, you can trick BCP into skipping a column by creating a view that references only the columns you want to export, thus skipping unwanted columns. Then use BCP to export the data from the view.

The following syntax shows how to export only the discounttype and discount columns from the discounts table:

create view discounts_view as
select output = convert(char(40),discounttype) + convert(char(5),discount)
from discounts

Next, create a format file that contains one column (see Figure 16.6). Only one column is listed in the format file because the view concatenates the discounttype and discount columns.

Figure 16.6.
The format file used to export data from a view.


Finally, use BCP to export the data from the view:

bcp pubs..discounts_view out discview.txt /f discview.fmt /U sa /P

The following is sample output:

Initial Customer 10.50
Volume Discount 6.70
Customer Discount 5.00

Modes of Operation

When importing data, BCP has two modes of operation: fast mode and slow mode. As you probably guessed, the fast mode runs faster than the slow mode. The performance difference is caused by the logging of transactions. Fast mode bypasses the transaction log; slow mode posts all data inserts to the transaction log.


NOTE: You need to be concerned with fast and slow mode BCP only when you import data. BCP does not use a fast or slow mode when you export data. When you run BCP, SQL Server automatically decides which BCP mode to run. There is no BCP switch that allows you to toggle between fast and slow modes.

Achieving Fast Mode BCP

In SQL Server 6.x, two factors determine whether BCP can run in fast mode: the SELECT INTO/BULKCOPY option and indexes. For BCP to run in fast mode, the following two conditions must be true:

If either of these conditions is FALSE, BCP runs in slow mode (see Figure 16.7).

Figure 16.7.
How SQL Server determines which BCP mode to run.

Why You Should Be Concerned with Which BCP Mode Is Running

You may be asking yourself, "Why not always run BCP in fast mode?" The answer is based on the following three factors:

Backup Strategy To run the fast mode of BCP, you must have the SELECT INTO/BULKCOPY option set to TRUE. By setting this option to TRUE, you may be sacrificing data recovery for BCP performance. When SELECT INTO/BULKCOPY is set to TRUE, you cannot back up the transaction log for a database. Instead, you can only back up the entire database. This means that you will be unable to use the transaction log to provide up-to-the-minute data recovery. Window of Opportunity Fast mode BCP requires that the target table not contain any indexes. This means that you must consider the downtime involved with dropping the indexes, loading the data, and re-creating the indexes. For a table that requires 24-hour data access, it is not feasible to drop and re-create indexes.


TIP: To significantly reduce the time required to create a clustered index, have your import data presorted on the fields that make up your clustered index. Then use the WITH SORTED_DATA option to create the clustered index, as in the following example:

CREATE CLUSTERED INDEX pk_idx ON table1 (id) WITH SORTED_DATA



TIP: The WITH SORTED_DATA option bypasses the physical data sort step normally used to create a clustered index.

Available Database Space A clustered index requires free space equal to approximately 120 percent the size of the table. For example, a 100M table requires approximately 120M of free database space to create the clustered index. If you are tight on disk space, you may not be able to drop and re-create a clustered index for a large table.

Table 16.6 helps clarify the differences between the two modes.

Table 16.6. Fast BCP versus slow BCP.
Fast Mode Slow Mode
PROS Fast! Operations are not logged. Don't have to worry about filling up the transaction log. Maximum recoverability.
CONS Zero recoverability. Must dump the database after using BCP. Cannot dump the transaction log. Indexes must be rebuilt after loading the data Slow! Every insert is written to the transaction log. Can easily fill up the transaction log during large data imports, thus complicating the import process.


NOTE: BCP may or may not notify you about which mode it is using. You do not receive a message that slow mode is in use when you import data into a table with an existing index.

BCP and Enforcement of Triggers, Rules, Defaults, Constraints, and Unique Indexes

When using BCP to import data into a SQL Server table, it is important that you understand how triggers, rules, defaults, constraints, and unique indexes are enforced. Many people forget that certain types of objects are bypassed when using BCP to import data. Table 16.7 summarizes which objects are enforced when BCP is used to import data.

Table 16.7. Enforcement of objects.
Object Enforced?
Default Yes
Unique index/unique constraints Yes
Primary key and foreign key constraints Yes
Check constraint No
Rule No
Trigger No


CAUTION: Do not forget that triggers, check constraints, and rules are not enforced when using BCP. To prevent data integrity problems, load your data into a work table and run it through a validation routine similar to the validation defined in your triggers, constraints, and table rules. Once you are satisfied that the data meets your integrity requirements, transfer the data to your target table.


NOTE: Before version 6.x, you could enforce primary key and foreign key relationships only through the use of triggers. Because BCP bypasses triggers, additional steps were required to ensure that you did not violate primary key and foreign key integrity. This problem has been resolved in version 6.x through the use of primary key and foreign key constraints, which are enforced by BCP.

Common BCP Traps

Be on the lookout for the following traps. They always seem to be lurking out there.

BCP Tips

Use the following tips to help simplify data imports and exports:

emp_id char(3)
hire_date datetime

CREATE VIEW date_example_view AS
SELECT emp_id,convert(char(12),hire_date,1)
FROM sample_table

bcp sales..date_example_view out sample.out /c /Usa /P


NOTE: Refer to the CONVERT function in Appendix D for other date formats.


NOTE: You do not have to issue an UPDATE STATISTICS command if the table's indexes are dropped before the BCP operation is performed and re-created after the BCP operation. Under this scenario, the statistics will be up to date.

CREATE PROCEDURE usp_load_example AS
/* flush out work table */
truncate table table1
/* BCP in data */
exec master..xp_cmdshell "bcp sales..table1 in C:\mssql\binn\table1.txt /c /Usa /P"
/* run summary procedures */
exec usp_summary1
exec usp_summaryN


CAUTION: Do not use xp_cmdshell to call BCP from within a user-defined transaction in a stored procedure. Doing so can lead to endless blocking!


TIP: Do you ever have to export or import data for all the tables in a database? To perform this task, you could manually create BCP scripts, but that can be tedious and time consuming--especially if you must use BCP to copy data from numerous tables in the database. An alternative to manually creating the scripts is to build a SQL statement that automatically generates the BCP syntax. Suppose that you need to export data from all the tables in the pubs database. To generate the BCP syntax, create a query that references the sysobjects table (each database in SQL Server has a sysobjects table and a corresponding record for each object in the database). In the WHERE clause of the query, specify type = `U' (this clause returns only user tables). The following is a sample query used to generate the BCP syntax (the -c switch is used in this example to export the data in a tab-delimited character format):

select `bcp pubs..' + name + ` out ` + name + `.txt' + ` -c -Usa -Ppassword -Stfnserver'
from pubs..sysobjects
where type ='U'
order by name

Following is the output from the query:

bcp pubs..authors out authors.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..discounts out discounts.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..employee out employee.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..jobs out jobs.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..pub_info out pub_info.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..publishers out publishers.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..roysched out roysched.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..sales out sales.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..stores out stores.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..titleauthor out titleauthor.txt -c -Usa -Ppassword -Stfnserver
bcp pubs..titles out titles.txt -c -Usa -Ppassword -Stfnserver

Now that you have the proper BCP syntax, you can save the output from the query to a BAT file and automatically run the file from the command line. As you see, the combination of SQL Server syntax with information from the system tables can simplify common DBA chores.


Alternatives to BCP

The following sections discuss alternatives to BCP. These alternatives are simple tips and tricks that can simplify data transfers.

SQL Server DBA Assistant

The SQL Server DBA Assistant allows you to graphically export data from SQL Server into common file formats. The product was developed using Visual Basic 4.0 and SQL-OLE technology.


NOTE: The SQL Server DBA Assistant is on the CD that accompanies this book. The product requires a 32-bit operating system (NT or Windows 95).

Follow these steps to use the SQL Server DBA Assistant to export data:

  1. Double-click the SQL Server DBA Assistant icon to start the SQL Server DBA Assistant.

  2. In the SQL Server DBA Assistant window, select the Bulk Copy tab (see Figure 16.8).

    Figure 16.8.
    The Bulk Copy tab in the SQL Server DBA Assistant window.

  3. On the Bulk Copy tab, select a database, a table, a file type, and any other options. Then click the Export Data button to export the data. When the export has completed, you receive a message specifying the number of rows exported (see Figure 16.9).

Figure 16.9.
The number of rows exported.

SELECT INTO and INSERT Combined with SELECT

The SELECT INTO statement creates a new table with a structure identical to the structure in the SELECT statement, along with any matching data. For example, the following SQL statement copies the authors table from the pubs database to the sales database:

SELECT *
INTO sales..authors
FROM pubs..authors


NOTE: The database option SELECT INTO/BULKCOPY must be set to TRUE if you want to use SELECT INTO to create a new table in a database. However, the SELECT INTO/BULKCOPY setting has no impact on your ability to create a temporary table in SQL Server.

An INSERT statement combined with a SELECT statement appends data into a target table with data from the source table. For example, the following SQL statement copies any rows returned from the SQL statement into the target_table:

INSERT INTO target_table
SELECT * FROM source_table


TIP: Sometimes you must drop and re-create a table to perform a table modification. Most people use BCP to copy out the data, drop and re-create the table, and then use BCP to copy the data back in. In this scenario, it may be complicated to use BCP to reload your data if you made extensive changes to your table. An alternative to using BCP is to use SELECT INTO to create a working copy of your table. Drop and re-create the table and then use INSERT INTO to reload the data from the work table.

Save SQL Output as a Text File

An easy way to save data in a fixed-width format is to issue a SQL SELECT query from the Query window in the Enterprise Manager. The query results are displayed in the Results tab. Click the Save Query/Result button to save the query's output. The output is saved in an ASCII text file in a fixed-width data format with newline separators (see Figure 16.10).

Figure 16.10.
Saving SQL output as a text file.


TIP: Use Query Options to prevent column names and row counts from being displayed in the Results tab. From the Query dialog box, click the Query Options button; the Query Options dialog box appears. From this dialog box, select the Query Flag tab. Set No Count Display to TRUE. On the Format Options tab, set Print Headers to FALSE.

SQL Server 4.2xs Object Manager

For some inexplicable reason, the graphical version of BCP found in SQL Server 4.2's Object Manager was not included in SQL Server 6.x. This is unfortunate because the graphical version of BCP in SQL Server 4.2x's Object Manager greatly simplified BCP usage. However, you can still run Version 4.2x's Object Manager against SQL Server 6.x to take advantage of graphical BCP. The following steps explain how to install and use version 4.2x's Object Manager to access the graphical version of BCP.


CAUTION: The Object Manager works only with version 6.x tables that use version 4.2x datatypes. This means that you cannot use BCP to copy tables that use identity, decimal, or other SQL Server 6.x enhancements.
  1. Load the object60.sql script included with version 6.x (see Figure 16.11). The file is located in the \mssql\install\ directory. Ignore any error messages generated by the script.

    Figure 16.11.
    The
    object60.sql script.

  2. Click the SQL Object Manager icon in the SQL Server for Windows NT group (see Figure 16.13). Remember that SQL Object Manager is a 4.2x product that is not included with SQL Server 6.x.

  3. In the Connect Server dialog box, enter the server name, login ID, and password for SQL Server 6.x. Click the Connect button to connect to SQL Server 6.x.


TIP: To determine which version of SQL Server you are logged in to, use the @@version global variable. For example:

SELECT @@version

Following is the output:

Microsoft SQL Server 6.50 - 6.50.201 (Intel X86)
Apr 3 1996 02:55:53
Copyright (c) 1988-1996 Microsoft Corporation


  1. Select a database.
  2. Click the Transfer button in the Object Manager. The Transfer Data dialog box appears; from this dialog box, you can use BCP to graphically copy data (see Figure 16.12).

Figure 16.12.
Object Manager's Transfer Data dialog box.

Transfer Manager

The Transfer Manager is a graphical tool used to transfer databases and objects. These objects can be transferred between different databases, different servers, and even different versions of SQL Server. Following are the types of objects that can be transferred:


TIP: The Transfer Manager is useful for moving a database from a development server to a production server or for moving a table and its data between the two servers.


NOTE: The Transfer Manager allows you to transfer objects and data between SQL Server 6.x and SQL Server 4.2x. Be aware that several new reserved words were added in SQL Server 6.x. If a reserved word is present in your 4.2x Transact SQL statement, the transfer to SQL Server 6.x will fail. The Transfer Manager also enables you to transfer a SQL Server 6.x database to a SQL Server 4.2x server. However, the database can- not contain any SQL Server 6.x language enhancements such as identity datatypes, decimal datatypes, declarative referential integrity, and so on.

The following steps explain how to use the Transfer Manager:

  1. From the Enterprise Manager, access the Server Manager dialog box and select a server. Open the server object and then open the database folder. Right-click the appropriate database. From the shortcut menu, select the Transfer option (see Figure 16.13).

    Figure 16.13.
    The Transfer menu option.


    NOTE: In version 6.5, the Transfer Manager is integrated with the Enterprise Manager. In version 6.0, the Transfer Manager was a separate application in the Microsoft SQL Server 6.0 (Common) group.

  2. From the Database/Object Transfer dialog box, enter the destination server and destination database. If necessary, set other options in the Database/Object Transfer dialog box.


    TIP: The destination server can be the same as your source server. Use this feature to transfer objects between different databases on the same server.



    NOTE: To transfer individual objects, deselect the Transfer All Objects checkbox. This enables the Choose Objects button. From the list that appears when you click this button, you can select individual objects to transfer.

  3. Click the Start Transfer button to transfer the objects and data (see Figure 16.14). If errors occur during the transfer process, click the View Logs button to review the error log.

Figure 16.14.
The Transfer Manager window.

Between the Lines

Following are important notes to remember when importing and exporting data:

Summary

As you can see from reading this chapter, you need to know a lot when it comes to importing and exporting data in SQL Server. The next chapter discusses common SQL Server errors.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.