SQL Server Export/Import excel using R script

In this post, let us see new approach to import excel into SQL Server and export SQL server data to excel. There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD.

BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly
BCP - Work around has to be done to include the header  
SSIS - With dynamic source & destination, handling mapping increases the complexity of the package
SQLCMD - Cannot export output in Excel

R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database and Java language extension support in SQL Server 2019, this new approach can be used extensively as it easy, fast and flexible.


SQL Server Export/Import excel using R script - Part 6

In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3, Part 4 & Part 5. In this post, let us see how to invoke sql files in a folder and export the output of the query as Excel files.

As a prerequisite, we need R packages "xlsx"  & "rJava", which I had showed in my previous posts on how to install and load them.

As we are going to make use of the powershell SQL Server cmdlets "Invoke-Sqlcmd", let us make sure SQL Server powershell modules are downloaded & installed from Powershell.

For this example, I have created below folder structure:






SQL Server Export/Import excel using R script - Part 5

In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3 & Part 4. In this post, let us see how to import multiple excel files in a folder (including sub folders) into different SQL Server table created dynamically based on excel file structure.


SQL Server Export/Import excel using R script - Part 4

In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 ,Part 2 & Part3 where we saw about exporting to Excel and importing to SQL Server table using "xlsx" package which is a powerful package but it has external dependency on Java ("rJava" package).

In this post, let us see how to import excel with multiple sheets using "readxl" package into different SQL Server tables.


I have done below example using SQL Server 2019 on windows 10 (64 bit) and R (3.4.4).
Refer my previous post on how to install R services

I have also used Wideworldimporters sample database for data setup in this example. 
Before trying to import excel with multiple sheets using "readxl" package into SQL Server tables, I tried to re-create the Excel file that was created using script provided in part 2

I got below error :

SQL Server 2019 Installation

In this post, let us see how to install SQL Server 2019 CTP 2.2 Evaluation Edition on Windows 10 (64 bit) machine.

SQL Server Export/Import excel using R script - Part 3

In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 & Part 2, where we saw about exporting to Excel. In this post, let us see how to import Excel into SQL Server table using R script. 

In part 2 post, I have exported few tables under "Sales" schema from "WideWorldImporters" sample database into single excel file with six sheets: 

Sheet 1 - CustomerTransactions
Sheet 2 - InvoiceLines
Sheet 3 - Invoices
Sheet 4 - OrderLines
Sheet 5 - Orders
Sheet 6 - SpecialDeals

While importing we have two scenarios  1) Excel file structure is defined & known
                                                                 2) Excel file structure is unknown and table has to be created dynamically

Let us see example for both scenarios:


SQL Server Export/Import excel using R script - Part 2


In continuation to my previous post on SQL Server Export/Import excel using R script - Part 1, let us see few other examples in this post.

In previous post, I had selected only first six key fields from [WideWorldImporters].[Sales].[Orders] table and passed this as inputdataset (dataframe) to write.xlsx2 function.

Instead if I do "SELECT * FROM [WideWorldImporters].[Sales].[Orders]", I got error as shown in below screenshot:

SQL Server Export/Import excel using R script - Part 1


In this post, let us see how we can easily import or export to excel with just few lines of r script executed from SSMS using sp_execute_external_script.

I have done below example using SQL Server 2016 on windows 10 (64 bit) and R (3.2.2).
Refer my previous post on how to install R services

Also I have used "WideWorldImporters" sample database for this example.

How to create linked server between On-premise and Azure SQL data warehouse

In my previous post, l wrote about how to create linked server between On-premise and Azure SQL database.

In this post, let us see how to create linked server between On-premise and Azure SQL data warehouse and after establishing linked server, how we can query Azure SQL data warehouse tables from local On-premise SQL Server.

I have created Azure SQL data warehouse in my previous post and for this example I have created table "dwtable1" within azure sql dw.

Below are the steps to publish linked server connection between On-premise and azure sql dw.


How to reset azure sql admin password

In this post, I have shared the resource which explains different ways to reset azure sql admin password

1) From Azure portal - >SQL Server Overview page -> Reset Password
2) From Powershell -> using Azure CLI command - az sql server update 
3) From Powershell -> using power shell command - Set-AzureRmSqlServer
4) From SSMS -> using t-sql command - ALTER LOGIN

Reset lost admin account password

See Also:

How to delete files in Azure data lake store

In this post, let us see how to delete files in Azure data lake store using powershell and Azure CLI commands.


I have created Azure data lake store with account name trndls and uploaded some JSON files.

How to copy multiple tables to Azure blob using Azure data factory

In my previous article, I wrote about introduction on ADF v2. In this post, let us see how to copy multiple tables to Azure blob using ADF v2 UI.

For this example, I have created tables named Test, Test1 within Azure SQL database - Source for the copy operation.

I have created Azure blob with Container called myfolder - Sink for the copy operation.

To copy multiple tables to Azure blob in JSON format, created
Pipeline1 - For Each activity with Copy activity
Pipeline2 - Lookup activity and Execute pipeline activity

Lookup activity provides the list of tables, output of Lookup are mapped to Object type parameter. For Each activity on each iteration (tables list in object parameter) provides the table name to source & sink within copy activity.

Below are the steps:

SQL Server Row level security

In SQL Server, implementing Row-Level Security enables to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context). 

In this post, let us see an example for RLS with Session_Context, advantage of this technique is we don't need to create separate users to enforce row level security.

We can implement RLS by creating SECURITY POLICY and predicates created as inline table valued functions. 

Let us consider this use case, an Insurance provider company database has all the agency company, its adviser, policy and commission details. Insurance provider has the application dashboard where any agency / individual adviser from an agency company can login (using single sign on) and view their details.

Requirement for Insurance provider is one agency company data should not be exposed to other company and only admins can modify the commission data within their company.

Temp tables in Azure sql database and Azure sql data warehouse

In this post, let us see about temporary tables in Azure SQL database and Azure SQL data warehouse.

Temp tables in Azure SQL database:


Global (DB scoped) & Local temporary tables are supported for Azure SQL Database and follow the same syntax and semantics that SQL Server uses.

Local temporary table (#table_name), 
Database scoped global temporary table (##table_name)

Azure data factory v2

Azure Data Factory visual tools enabled in public preview few days back. In this post, let us see how the look and feel is and whats new in ADF v2.

From Azure portal, while creating Azure data factory we need to select the version as v2, once created click on Author & Monitor.




In my previous post, I had shared an example to copy data from Azure blob to Azure cosmos DB using Copy data wizard.


How to concatenate values with specified delimiter

In SQL Server 2017, new function is introduced to concatenate values with specified delimiter

In SQL Server 2012, Concat() was introduced. Only difference between Concat() and Concat_WS(), Concat_WS() allows delimiter to be specified to concatenate values.

Both CONCAT & CONCAT_WS ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting as shown in below image.

How to combine separate row values into delimited string

In one of my old post, I wrote about how to combine separate row values into delimited string. Now starting from SQL Server 2017, new string function was introduced to perform this string aggregation easily. Let us see few examples in this post.

DECLARE @Tmp TABLE (ID INT,
                    Name VARCHAR(30))
INSERT INTO @Tmp SELECT 1,'SQL Server'
INSERT INTO @Tmp SELECT 2,'Oracle'
INSERT INTO @Tmp SELECT 3,'DB2'
SELECT * FROM @Tmp

SELECT STRING_AGG (Name, ',') FROM @Tmp

How to split delimited string into separate row

In this post, let us see how to split delimited string into separate row. 

In previous versions of SQL Server, this can be achieved using User defined function

When support for querying JSON data was introduced in SQL Server, using OPENJSON seems to be simpler technique to split delimited string into separate row.

Finally starting from SQL Server 2016, new built string function was introduced to do this. Let us see few examples with various delimiters:

How to load data into Azure SQL Data Warehouse using SSIS

In this post, let us see how to load files from local folder into Azure SQL Data Warehouse using Azure SQL DW Upload Task in SSIS.

We need to download and install Azure Feature pack to get the Azure components in SSIS.

Azure SQL DW Upload Task is designed to work in such a way that it consumes the files in local folder and places in Azure Blob storage as staging (there is an option to retain files within Blob storage)  and then it is loaded into Azure SQL data warehouse.


How to upload files into Azure blob storage using SSIS

In this post, let us see how to upload files from local folder into Azure blob storage using SSIS.


In the SSIS Toolbox, we can see Azure component grayed out.



How to create linked server between On-premise and Azure SQL database

In this post, let us see how to create linked server between On-premise and Azure SQL database and after establishing linked server, how we can query Azure SQL database & create objects in Azure SQL database from local On-premise SQL Server.

In previous posts, I wrote about 

How to play video or music from SSRS

In this post, I will show how to play video or music from SSRS using two options.

Option 1: Using SSIS package that invokes the video or audio file that is stored in SQL Server filetable (or normal windows directory folder)

To see an example on SQL server filetable, refer my previous post.