Rajendra Gupta

SQL Server Data Import using SQL Operations Studio

September 18, 2018 by

SQL Operations Studio is a new GUI based tool that works on Windows, Mac OS and Linux operating systems. It connects to SQL Server, Azure database, and SQL DW.

Database administrators and developers use SQL Server Management Studio to import the data into SQL Server. Is there any way to do so in SQL Operations Studio? Let us explore this in further sections of the article.

Prerequisite:

Before we move further, you can go through below articles to get an overview of SQL Operations Studio.

Getting started with SQL Operations Studio; initial installation and configuration

In this article, we are going to explore the below points

  • Extensions in SQL Operation Studio
  • Importing data into SQL database using SQL Operations Studio
  • Common errors

SQL Operation Studio release versions information

If you want to know about installed SQL Operations Studio version, click on Help ->about

We can see detailed information about SQL Operation Studio release version.


SQL Operations Studio provides new functionality through the Extensions. Extensions provide a nice way to add more functionality to the base SQL Operations Studio product.

Some of the important extensions available in the SQL Operation Studio are as below:

Extension

Comment

Redgate SQL Search

SQL Search is a free add-in that lets quickly search for SQL across databases

Server Reports

This gives information about server performance with below reports

  • DB Space Usage
  • DB Buffer Usage
  • CPU Utilization
  • Backup Growth Trend
  • Wait counts

SQL Server Profiler

We can use this extension to trace SQL similar to SSMS Profiler.

SQL Server Agent

SQL Server Agent extension gives the ability to manage and troubleshoot SQL Server Agent jobs, alerts, operators, proxy etc.

SSMS Keymap

We can get SSMS keyboard shortcuts using this extension.

SQL Server Import

It allows importing flat files (.csv, .txt) or JSON files into SQL Server database.

SQL Server Management Studio (SSMS) is one of the most important tools for SQL Server DBA’s and Developers to complete their work on a daily basis. In my previous tip, SQL Server management studio import wizard, we learned that SQL Server Management Studio Flat File Import Wizard simplifies importing the data from the flat files to SQL Server tables with an intelligent process. This process is good enough to predict the incoming data, identify patterns, types, delimiters etc.

Below is the screenshot of the Import Flat File Wizard in SQL Server Management Studio 17.x

The SQL Operations Studio August-2018 release provides the functionality of data import using flat files with Import Wizard.

The overall process for the data import in SQL Operation Studio is as follows:

In order to use SQL Server import in SQL Operations Studio, we need to install it from the Marketplace. Click on the ‘SQL Server Import’ Extension from the marketplace and it opens up the web page on the right side of the SQL Operation Studio.

We can also note here that SQL Operation Studio recommends this extension.

Click on install (Green button) to download and install it.

Once we click on Install, we can see in the Output window, it downloads the extension file from the link.

Once the download is complete (approx size 35 MB), it installs Flat file import service to the SQL Operation Studio. Click on the reload to activate this extension.

Import data using Import File Wizard

First, let us prepare data to import. I am going to use top 100 records from the Sales. Invoices table of WideWorldImporters database.

Select the data and save it as. CSV format. We can also use shortcut [Ctrl+R Ctrl+C] to save data as.CSV file.

Save the.CSV file at the desired location.

Finally, click on Save. We get the pop-up message to indicate that the file saved successfully. It also opens up the.CSV file in the new query window.

Now in order to import the data, Right click on the destination database (we can change it later in wizard too) and click on Import wizard or press the shortcut key Ctrl+I


Below is the Import flat file wizard.

Steps to import data using the Import flat file wizard are as follows:

Step1: Specify Input File:

In the first step, we need to specify the input file, table name (default table name is same as the flat file name), table schema. We can also select the database name later from the drop-down databases list.

We can also select the table schema from the drop-down list. Click on Next to move to next step

Step 2: Preview Data

In the next step, we can see the data preview from the flat file. Review the data and click on Next

Step 3: Modify Columns

Import flat file wizard decides the appropriate data types for each column.

We can modify column names, data type, and primary key column. We can also see whether a null value is allowed or not in the column.

Let us go with the default values this time. Click on Import data to proceed.

Step 4: Summary

In the next step, it shows a summary of the information such as server name, database name, table, schema, table name and the file to be imported.

It also shows the import status, whether it is successful or not. Below we can see that import is successful.

We can verify the table and data into the destination database.

Import data using modify columns

In the previous section above, we have not modified any property in the column such as data type. We did not define a primary key on the table.

To make the process simpler, I have only taken 10 records in the flat file with few columns to import into table now.

Follow the wizard as shown in the steps above.

Now let us change the following things:

InvoiceID: Define Primary key on this.

Comments: Change data type from nvarchar(50) to nvarchar(100).

Click on Import data to start data import.

Now let us view the table properties to verify the changes. We can see below the changes we defined during the import process.

InvoiceID: Primary key exist in this column

Comments: data type is nvarchar(100) now.

Common Errors in Import Flat file Wizard:

  • If we are performing export results to CSV from any databases, the file should be unique in the destination folder and should not be open by any process. Otherwise, we get below error message

  • We might receive Import status as Error creating table.

  • This is general error and it might be due to

    • Destination table already exists: Destination table name should be unique else, it might give the below error message.
    • You do not have adequate permissions in the database to insert data.

  • If someone has opened up the flat file, which we need to use the import operation, Preview data wizard, just shows up small progress circle and does not give any error message.

Conclusion

Importing flat files using SQL Operations Studio is an important feature for both SQL developers and DBAs as it is a common requirement to import data.

SQL Operations Studio contains many important extensions. We will review more on this in future articles.





Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
506 Views