SQL Server Reference Guide

One of the first duties you’ll have as a Database Administrator (DBA) or Database Developer after you design a system and secure it properly will be to move data in and out of the system. It’s such a common task that there are several ways you can do it. SQL Server, from the earliest versions, provides tools and processes that can handle almost any format you can think of to bring data into and out of a database.

In this overview and tutorial, I’ll show you how to move data in and out of SQL Server, I’ll explain all of the processes you have available, and then explain any differences between the versions. I won’t be able to cover each tool in depth, but I’ll point out links so that you can get started right away. Along the way, I’ll hyperlink to lots of other tutorials, so if you’re new to SQL Server you can just follow those for anything you aren’t familiar with.

Data Types

Before I begin, it makes sense to recall the data types that you have available in a database system. I have an entire article on that here, which you should read thoroughly before you move on through this article.

Why is that so important? Well, the whole point of a database system is to be an authoritative data store. If you can’t depend on the fact that the data is correct in the system, you can’t depend on anything else.

So it follows that the data is in the format that you expect — and the very bottom level of data is the type for a particular datum. What that means is that a number that might need to have math performed on it should be numeric — not text. For instance, 1234 can be stored as many different types of data, but if it represents a code it will be treated differently than if it needs to be added to another number.

You need to understand both the source and destination formats so that you get what you want. You normally want to transfer the data from the source into as close a representation as you can for the destination. In other words, a number should be in the numeric format on the source and numeric on the destination and so on.

Sometimes that isn’t possible. If you’re coming from a duplicate platform (such as transferring SQL Server database data to SQL Server database data) this is straightforward, but if you’re coming from Oracle or Excel, the data types might not match up exactly. Dates are a classic example of this issue. It’s even more pronounced when you import or export to or from a text file — text doesn’t have data types.

I’ll explain along the way how to deal with these situations and which method is best for a given application.

Key Restrictions

Not only do you need to focus on the data types, you need to focus as well on the Primary Keys in your system and other relationships such as Foreign Keys and Unique Indexes. The primary issue here is to get the data in at the right “time,” meaning that the order matters. If you have data in a “child” table, you have to put the data into the “parent” table first, or the insert will fail. Of course, you can turn off the constraints by suspending or dropping the keys, but that’s often a very bad idea.

The point is, you’ll have to treat the import just like a standard insert operation from an application. It needs to follow the same path. For exports, of course, you don’t have this issue, but at some point you are using that exported data somewhere else, so you need to think about it.

While you’re there, make sure you take into account other restrictions such as indexes, and whether you’ve set the Primary Key to be auto-generated (using the Identity qualifier) and so on.

In any case, there are two general methods for getting the data in or out of SQL Server — manually, meaning that you have to open a tool and click or type, and automatically, meaning that you can set a schedule or a trigger for the import or export to happen without your intervention. Of course, an automated method can certainly be used manually.

I’ll arrange these in order of simplicity, although there will be a bit of my own opinion here. If you’re more familiar with working with commands, for instance, you might find that a command-line tool is better for you than a “Wizard” that guides you through the process.

Import and Export Methods: Manual

Manual methods are best when you just need to perform an import or export operation once. They require you to be at a console somewhere, although not necessarily at the server, mind you, you can run some of these tools from a client console.

Queries

The first and simplest way to get data in and out of SQL Server is to use the query tools. In SQL Server 2000, this includes Query Analyzer (QA) and osql and in SQL Server 2005 and higher you’ll use SQL Server Management Studio (SSMS) in a Query Window and SQLCMD.

To export data in QA and SSMS, you have a few options. One is to run the query, select all the data in the results pane (left-click the small box in the top left of the results pane and press CTRL-C) and then paste the results into Excel or a notepad file. Save the results and you’re done. Of course, this has the most danger of losing the data types, but it does work.

You can also right-click the results pane and select “Save As” to create a text file output. Not only that, in both QA and SSMS you can select “Output to File” before you even run the query, using the icon bar.

Another interesting use of QA and SSMS is to “build” a set of insert statements you can use in another table or database. Assume for a moment that you have a table in another database on a different server that you want to transfer the first and last names of the “authors” table into. Assume that it has only two columns: FirstName and LastName.

One possible method is to use the following kind of statement to create the statements and then paste the results into the query tool on the other system:

USE pubs;
go
SELECT 'INSERT INTO othertable (FirstName, LastName) VALUES 
(' + '''' + au_fname + +'''' + ',' + '''' + au_lname + ''''+ ')'
FROM authors;
GO

The basic idea is to get those single-quotes (ticks) correct. Adding four of them makes one tick.

To move data from one table into another in the same database, it’s much simpler. You just use a subquery. Assume again that table with just the first and last names:

USE pubs;
GO
CREATE TABLE test (FirstName varchar(255), LastName varchar(255));
GO
INSERT INTO test (FirstName, LastName)
SELECT au_fname, au_lname FROM authors;
GO

SELECT * 
FROM test;
GO

I’ll cover osql and SQLCMD in a moment.

Import/Export Data and the Copy Database Wizard

By far the easiest method to use to move data in and out of SQL Server is to right-click the object in Enterprise Manager (EM) or SSMS and select “Tasks” (or “All Tasks”) and then follow the instructions in the Wizard. In fact, the Wizards use DTS or SSIs (more on those next) but it simplifies the process. In SQL Server 2005 and higher, another option called “Copy Database” on that same right-click menu will transfer an entire database if that’s what you’re after, but I find that Backup and Restore works better for that.

Import and Export Methods: Automated (or can be)

Although the previous commands and features require you to be at the console, you have other options that you can use for manual imports or exports that can also be scheduled — in some cases you can use SQL Server Agent, within SQL Server commands.

Other commands, in fact all of the command-line versions you see below, can be automated even within Windows, using the Windows scheduler or even third-party Windows scheduling tools.

One caveat I would mention here — any of the automatic methods should include logging and alerting in some way. You need to be able to tell that they ran, or didn’t run, how long they took, and whether they completed successfully. I’ve covered that in other articles on this site, including the PowerShell area.

The Bulk Copy Program (bcp)

Since the very first versions of SQL Server, the Bulk Copy Program (bcp.exe) has been one of the easiest and fastest ways to export or import data. It’s a command-line program that you can find out more about by typing bcp /?:

C:\>bcp /?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
 [-m maxerrors]      [-f formatfile]     [-e errfile]
 [-F firstrow]       [-L lastrow]       [-b batchsize]
 [-n native type]     [-c character type]   [-w wide character type]
 [-N keep non-text native] [-V file format version] [-q quoted identifier]
 [-C code page specifier] [-t field terminator]  [-r row terminator]
 [-i inputfile]      [-o outfile]       [-a packetsize]
 [-S server name]     [-U username]      [-P password]
 [-T trusted connection]  [-v version]       [-R regional enable]
 [-k keep null values]   [-E keep identity values]
 [-h "load hints"]
C:\>

I’ve covered this tool in another tutorial, but the important thing to keep in mind is that you have two basic formats it can work with: “text,” and “native.” Text is just that, ANSI text. You’ll lose all of your data types this way. Here’s an example of sending the entire “authors” table from my SQL1\SQL2K instance (also works with the later versions) to a text file called “test.txt”:

bcp authors out -SSQL1\SQL2K -T -oc:\temp\test.txt

You can also use the “native” format, which means that it will keep the type formats and so on, but only for SQL Server. All you have to do to use that is to add the –N qualifier to the end of the statement above.

PowerShell

I’ve covered PowerShell a lot, and it’s a command-line tool with a thousand (or more) uses. It has the ability to talk to anything in Windows, and can also select data from SQL Server and send it to an output file. I’ll do that here.

I’m not including error handling, logging and alerting in this example so that I can focus on the “meat” of the process, but you absolutely should. In the following example, I’m using PowerShell and the AdventureWorks sample database on my server called “UNIVAC”.

The result is a file that has each element separated by a colon, but you have a ton of control over the format and placement of these data elements. Also — this presumes that you have the SQL Server client libraries available on the system where it runs from:

# SQL Server Query using SQL Data Adaptor
$serverName = "UNIVAC"
$databaseName = "AdventureWorks"
$qry = @"
SELECT * FROM Production.Product
"@
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($qry,$connString)    
$dt = New-Object "System.Data.DataTable"    
[void]$da.fill($dt)    
$dt | out-file c:\temp\test.txt

DTS

The name says it all — Data Transformation Services (DTS). This graphical tool, available in SQL Server 2000, allows you to select the objects you want to work with and then perform actions on them, including copying them. I’ve covered this tool here and there is a lot more information on it at InformIT.

Although you can still run a DTS package in SQL Server 2005 and higher, it’s best to use SSIS, which I’ll explain next.

SSIS

SQL Server Integration Services is the replacement for DTS in SQL Server 2005 and higher. It is aimed at the Extract, Transform and Load process in Business Intelligence, but you can use it for much more.

I’ve covered this tool in more depth here, but like DTS it also creates “packages” that are a group of objects and actions you can perform on them such as transferring data in and out. I usually start with the Import and Export data Wizards, and then save those as an SSIS package to work with later.

BULK INSERT

The BULK INSERT statement is similar to the bcp.exe program, except that you type it in a Query Window in QA or SSMS or other query tool. It can read data from a text or native SQL Server file and import it into the database you’re using.

It has similar qualifiers, such as native or text formats. The primary difference with this tool is that it doesn’t export anything — it’s import only. Read more about it here.

SQLCMD and osql

I’ve covered these two tools in other tutorials that you can read here, but the idea is that they provide a command-line query interface to SQL Server. The osql tool is available in SQL Server 2000 and higher, and SQLCMD is available in SQL Server 2005 and higher.

With these tools, you enter the name of the server, database and a query you want to run. They both contain a switch (-o) that will send the results of the query to a file. You can treat this similar to the “Output to File” option in QA and SSMS.

Here’s a quick sample that runs the same select statement as the PowerShell script:

sqlcmd -S UNIVAC -d AdventureWorks -E -Q "SELECT * FROM Production.Product" –o  c:\temp\test2.txt

And of course these are only the primary methods you have. There are others, which I’ll cover in later tutorials.

InformIT Articles and Sample Chapters

If you’re using DTS, make sure you check out this free chapter from SQL Server DTS, by Jim Samuelson and Brian Sullivan, Enhancing DTS Package Performance.

Books and eBooks

SSIS is a huge program with a lot of study required. Happily there’s a book here for that: Microsoft SQL Server 2005 Integration Services. (Also available in Safari Books Online)

Online Resources

If you need a blow-by-blow for the Import and Export Wizard, you can start here.