Easy Way to Import and Export SQL Server Data

I know from my personal experience that many SQL Server novice users have issues while uploading and downloading data in various formats. In my practice, this kind of tasks arise very often. I would prefer to automate the process for the recurrent tasks, if the file structure is not going to be changed. I can create a SSIS package, deploy it on a server and then schedule one through the SQL Agent.

In practice, clients operate with numerous data formats (excel and xml). In some cases, Import and Export Wizard included in Microsoft SQL Server Management Studio helps greatly. However, I prefer dbForge Data Pump for SQL Server a new SSMS add-in from Devart. The tool allows me to quickly and easily upload and download data in a variety of formats.

Recently, I needed to upload data from a large XML file with quite simple structure:

<users>

<user Email="joe.smith@google.com" FullName="Joe Smith" Title="QA" Company="Area51" City="London" />

</users>

Let’s compare three approaches to resolving the problem: SSIS, XQuery, and Data Pump. We cannot use the SSMS Import and Export Wizard as it does not work with XML.

1. SSIS

In Integration Service, create a new Data Flow Task:

On the Data Flow tab, select the XML data source. Then we need to specify the XML file to load data from and generate the XSD schema:

Since all XML values are stored in the Unicode, I need to convert them to ANSI for the following insertion:

Then we need to specify where to insert data:

We need to specify the table to insert data to. In this example, I created a new table.

Then just press the Execute Results and check the package for errors:

The SSIS package creation process took 10 minutes. The package ran for 1 minute.

2. XQuery

Often, writing a query is much faster than creating a SSIS package:

IF OBJECT_ID('dbo.mail', 'U') IS NOT NULLDROP TABLE dbo.mail
GODECLARE @xml XML
SELECT @xml = BulkColumnFROM OPENROWSET(BULK 'D:\users.xml', SINGLE_BLOB) xSELECTEmail = t.c.value('@Email', 'VARCHAR(255)')
, FullName = t.c.value('@FullName', 'VARCHAR(255)')
, Title = t.c.value('@Title', 'VARCHAR(255)')
, Company = t.c.value('@Company', 'VARCHAR(255)')
, City = t.c.value('@City', 'VARCHAR(255)')INTO dbo.mailFROM @xml.nodes('users/user') t(c)

It took me 3 minutes to create the query. However, the execution time exceeded 9 minutes. The reason is that parsing server-side values is quite expensive.

3. dbForge Data Pump for SQL Server

Now’s the time to try Devart’s Data Pump.

In the Database Explorer shortcut menu, select Import Data.

Then we need to specify the file type and the file path.

Then we can select a table to insert data. The table can be created automatically.

Select the XML tag for parsing:

Now we can select what columns we need (from the selected tag).

Now we need to check the Use bulk insert option to reduce the load on the log file when inserting data.

Click Import and that’s it! The whole process took 2 minutes. This approach does not require any special qualification. The tool allows me to quickly and easily upload data in any formats convenient for my clients.

Reference: Pinal Dave (http://blog.sqlauthority.com)