SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bulk Inserting Build Data–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the common tasks that many DBAs need to do is insert data into a database. Often this comes from various sources, but a CSV (comma separate value) format is common. One could use the data import wizard, but that seems to be very flaky with CSVs, so I’ll show a quick way to use the BULK INSERT command.

This command is a way to read files and load them into a table, similar to how bcp works. However, this is a T-SQL command, and can be included inside your database.

The basic format is

BULK INSERT <table>

FROM <source>

WITH <options>

For most CSV imports, this means we need to pick a table, in my case, the BuildStaging table, and a source file. My statement looks like this:

BULK INSERT dbo.BuildStaging
FROM ‘e:\Documents\ssc\BuildList_SQLServer2014.csv’

I also need some options. The basics for a CSV are:

WITH
(   FIELDTERMINATOR = ‘,’,
     ROWTERMINATOR = ‘\n’
);

There could be other items you want to enable, and there is quite a list. In my case, my file looks like this:

2017-08-24 14_26_20-E__Documents_ssc_BuildList_SQLServer2014.csv - Sublime Text

I have a header row, so let’s get rid of that by adding a FIRSTROW = 2 option.

Now when I run my command, the data is inserted.

2017-08-24 14_28_12-SQLQuery1.sql - (local)_SQL2016.SSBuilds_1_Dev (PLATO_Steve (62))_ - Microsoft S

From here, I need to work with the data and clean it futher for insert into other tables.

SQLNewBlogger

This was a quick task I needed to accomplish. I knew most of the syntax, but had to double check the option names, and ended up taking about 2 minutes to import the data and 10-15 to write this post.

And, I’ll likely remember how to do this import after spending time writing about it.


Filed under: Blog Tagged: ETL, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...