Often you may require exporting data from SQL Server to other locations with a different structure such as XML, JSON or CSV.
From the earliest versions of SQL Server, the Data Transformation Wizard was a part of the tools in converting data in the SQL Server tables to other formats.
Herein I describe how to transform data in the Northwind Database tables to a delimited text file using the wizard in a couple of easy steps.
In Step 1 you need to bring up the wizard. It is easy and you can do it from SQL Server Management Studio as shown in this figure below.
Step1_jpg
You click on Export Data... to invoke the wizard who will guide you through the process. Actually there is a welcome scree after which this screen is displayed. You need to choose a data source from which you want to export data. The program finds a server already installed and it also uses the default authentication, namely Windows Authentication. If you have followed SQL Server 2012 installation in my blog you would notice that this was the type of authentication configured at installation time. Using the Refresh screen the Northwind database is also chosen as the database chosen from which data will be exported.
Step11.jpg
Click Next brings up the next step in the wizard as shown. The Choose a Data Destination window is displayed. since we plan on exporting data into a delimited text format, create an empty file (herein FFD.txt) with extension text(CSV) and save it to a location of choice. For Destination choose Flat File Destination. Browse and locate the empty file you created. You may accept other defaults unless you want to change.
Step2.jpg
Click Next to get to the next step as shown. We will use a query to specify the data to be transformed.
FROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
Where [Order Details].UnitPrice>100
From the earliest versions of SQL Server, the Data Transformation Wizard was a part of the tools in converting data in the SQL Server tables to other formats.
Herein I describe how to transform data in the Northwind Database tables to a delimited text file using the wizard in a couple of easy steps.
In Step 1 you need to bring up the wizard. It is easy and you can do it from SQL Server Management Studio as shown in this figure below.
Step1_jpg
You click on Export Data... to invoke the wizard who will guide you through the process. Actually there is a welcome scree after which this screen is displayed. You need to choose a data source from which you want to export data. The program finds a server already installed and it also uses the default authentication, namely Windows Authentication. If you have followed SQL Server 2012 installation in my blog you would notice that this was the type of authentication configured at installation time. Using the Refresh screen the Northwind database is also chosen as the database chosen from which data will be exported.
Step11.jpg
Click Next brings up the next step in the wizard as shown. The Choose a Data Destination window is displayed. since we plan on exporting data into a delimited text format, create an empty file (herein FFD.txt) with extension text(CSV) and save it to a location of choice. For Destination choose Flat File Destination. Browse and locate the empty file you created. You may accept other defaults unless you want to change.
Step2.jpg
Click Next to get to the next step as shown. We will use a query to specify the data to be transformed.
Step4.jpg
Click Next. We need to provide a query to get the data from the database. Here is the query that is going to be used.
===========================================
SELECT Products.ProductName, [Order
Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate, Orders.RequiredDateFROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
Where [Order Details].UnitPrice>100
===========================================
The next window needs a query to be processed.
Step5.jpg
Insert the previously shown query. You can hit the Parse button to verify that the query is good. You can also Browse and get the query from a saved file.
Click Next to display a screen to configure the destination as shown.
Step 7.png
Click Next. The summary screen is displayed as shown.
Step 8.png
You are almost done. Hit the Finish button. The program processes your request and comes up with success or failure. In this case success.
Step 9.png
You are done. You have successfully exported data to a delimited text format from SQL Server Tables.
No comments:
Post a Comment