Import data in SQL Express Management Studio

One of the restriction of the Sql Express Managent Studio is not allowing import/export of data. To overcome to this limitation we can either download the Sql Server Express Edition Toolkit or we can use the BULK INSERT statement in order to import a file.

This will be our document:

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt -- john
%
ut labore et dolore magna aliqua. Ut enim ad minim veniam -- mario
%
sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaera -- ugo
%
quis nostrum exercitationem ullam corporis suscipit laboriosam -- anne
%
vel illum qui dolorem eum fugiat quo voluptas nulla pariatur -- vera

We create first a temporary table and store the content of the file in it:

CREATE TABLE #TempTbl
(
    Phrase nvarchar(max)
)

BULK INSERT #TempTbl
FROM 'C:\temp\sampleCommaSep.csv'
WITH
(
    ROWTERMINATOR='%'
)

It is possible to specity the column delimiter of the csv specifing  FIELDTERMINATOR in the query:

CREATE TABLE #TempTbl
(
    Phrase nvarchar(max),
    Author nvarchar(150)
)

BULK INSERT #TempTbl
FROM 'C:\temp\sampleCommaSep.csv'
WITH
(
    FIELDTERMINATOR = '--',
    ROWTERMINATOR='%'
)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s