Lesson Learned #504: Inserting Records from a CSV File using OPENROWSET into TempDB

[Jose_Manuel_Jurado]

Today, I would like to share how to insert records from a CSV file stored in Azure Blob Storage into a temporary table in TempDB in Azure SQL Database using OPENROWSET and a format file.

First, we need to create a database scoped credential and an external data source to access the Azure Blob Storage.

CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH
    IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sv=2022-11-02&ss=bfqt&srt=sco&sp=rwdlacupiytfx&se=2024-06-30T16:56:42Z&st=2024-06-21T08:56:42Z&spr=https&sig=tAjokk5xO9kc%3D';

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://blobstorage.blob.core.windows.net/demo',
    CREDENTIAL = MyCredential
);

In this situation, I would like to create a format file that defines the structure of the CSV file and how it maps to the table columns. Below is an example format file in XML. 

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Key" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="NumData" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="Col_01" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="4" NAME="Col_02" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="5" NAME="Col_03" xsi:type="SQLNVARCHAR"/>
  </ROW>
</BCPFORMAT>

Save this format file and upload it to your Azure Blob Storage container. 

Finally, we use OPENROWSET function to load data from the CSV file using the format file and insert it into a temporay table in TempDB.

SELECT *
INTO #test1
FROM OPENROWSET(
    BULK 'test1.csv',
    DATA_SOURCE = 'MyDataSource',
    FORMATFILE ='format.xml',
	FORMATFILE_DATA_SOURCE = 'MyDataSource', 
	FIRSTROW = 2 
) AS rows;