Several tools for importing and exporting data from common SQL Server

Excerpt from: http://www.cnblogs.com/chenxizhang/archive/2011/06/09/2076542.html

In our daily work, there are more and more opportunities to deal with databases. This article I'll tidy up a few tools for common SQL Server import and export data

1. Data Import and Export Wizard

This is a visual tool that I put in the first place because it can be very flexible to meet the import and export function, and is WYSIWYG, easy to use.

There are many ways to start the Data Import Export Wizard, and I am accustomed to starting with the following command (start = "Run")

Dtswizard (as the name implies, it is a wizard--wizard and is related to dts--data transfomation service)

As you can see, this tool supports many different types of data sources (as well as data targets), which are not confined to SQL Server servers.

"Note" If it is 64-bit, Excel and access are not found in the provider here (I know many friends want to export both formats)

The wizard can also be started in SQL Server Management Studio (SSMS). This is more convenient if the data source or the data destination is SQL Server.

"Note" The wizard launches in such a way that you can see Excel and access (it's amazing,)

It is worth mentioning that this import and Export Wizard also has the benefit of saving the operations that we often need to import and export, as shown in

What does it mean to have a so-called SSIS package? SSIS refers to the SQL Server integration Service, which is an important component of the Microsoft SQL Server BI platform for designing and managing ETL solutions.

This SSIS package is a special file extension dtsx that can be opened for viewing through a so-called Business Intelligence developement Studio (BI Studio), and can be further edited

The discussion of the "Remarks" SSIS is beyond the scope of this article. If you are interested, please search my other articles.

2.BCP

If you want to implement a simple data import and export, and you want to use the script command, rather than the graphical interface to implement. You might consider the bcp utility provided by SQL Server.

Demonstrates how to export a table as an Excel file, but if you want to export from a query, you can follow the syntax below

You can also use BCP to make data, as long as you change the out to in.

"Note" The biggest problem with using bcp to export data is that there is no header row

3. Bulk INSERT and OPENROWSET

If you want to import the data of an Excel file directly in T-SQL, or the data of a TXT file, you can learn about the following two special T-SQL syntax

Bulkinsert's syntax is roughly as follows

OpenRowset's syntax is roughly as follows

Remarks for detailed usage of these two statements, refer to the Books Online from SQL Server.

4.FORXML and OpenXML

If you want to export data that is imported into XML format, you can learn about the Forxml and OPENXML syntax (they are T-SQL syntax, so you can also embed it in our stored procedures with flexibility)

Use the Northwind GO from theOrders for     XML RAW (' OrderItem '), ELEMENTS xsinil,  ROOT (  

The syntax above, you can return the query in XML format, as shown in

The memo FOR XML is a clause of select and for more usage, refer to SQL Server's own Books Online

Notes are exported to XML format for better sharing between different applications.

Conversely, if we get a piece of XML data, we want to import it into a table in SQL Server. You can consider using the OPENXML syntax. Its role is to restore the XML to the rowset data, and then it can be inserted into our destination table.

DECLARE @x XMLDECLARE @docHandleIntSET @x=n' <orders xmlns:xsi= ' http://www.w3.org/2001/XMLSchema-instance > <OrderItem> <orderid>10248</ orderid> <CustomerID>VINET</CustomerID> <EmployeeID>3</EmployeeID> <orderdate>19 96-07-04t00:00:00</orderdate> <RequiredDate>1996-08-01T00:00:00</RequiredDate> <shippeddate >1996-07-16T00:00:00</ShippedDate> <ShipVia>3</ShipVia> <freight>32.3800</freight > <shipname>vins et alcools chevalier</shipname> <shipaddress>59 rue de labbaye</shipaddress > <ShipCity>Reims</ShipCity> <shipregion xsi:nil= "true"/> <shippostalcode>51100</s hippostalcode> <ShipCountry>France</ShipCountry> </OrderItem> <OrderItem> &LT;ORDERID&G t;10249</orderid> <CustomerID>TOMSP</CustomerID> <EmployeeID>6</EmployeeID> <ord Erdate>1996-07-05t00:00:00</ordeRdate> <RequiredDate>1996-08-16T00:00:00</RequiredDate> <shippeddate>1996-07-10t00:00:00  </ShippedDate> <ShipVia>1</ShipVia> <Freight>11.6100</Freight> <shipname>toms Spezialitäten</shipname> <shipaddress>luisenstr. 48</shipaddress> <ShipCity>Münster</ShipCity> <shipregion xsi:nil= "true"/> <shipposta Lcode>44087</shippostalcode> <ShipCountry>Germany</ShipCountry> </orderitem></ Orders> '--first step, prepareEXEC sp_xml_preparedocument @docHandle output,@x--Second step,openxmlinsert Orders SELECT *  from OPENXML (@docHandle, N'/orders/orderitem ', 2) with orders--third step, destroy EXEC sp_xml_removedocument @docHandle 

Note OPENXML There are other more complex uses, please refer to the SQL Server bring online books

5. Export data using Excel, or create a query

Finally, we introduce a simpler method, if you often need to query the database in Excel, and then do some further analysis. The best way to do this is to export the data directly in Excel, or create a query

The benefit of this approach is that you can put the data you need anywhere in the existing Excel, and be aware that the data is linked to the database, that is, if the data of the database has been updated, just refresh it.