Solved

How to use the BULK INSERT SQL Statement

Posted on 2013-11-11
11
1,481 Views
Last Modified: 2016-02-11
I need to bulk insert from a tab delimited text file into a SQL Server table.

The SSIS Wizard works when I select FileSystem.

I need to run the same operation from a tsql statement.

BULK INSERT SymbolWork1
      FROM 'D:\\inetpub\\stockpickermax\\Data\\AMEX.TXT'

      WITH
      (
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '0x0A',  ?? What is the syntax for CR/LF
      FIRSTROW = 1  ?? What is the syntax to skip the first row of the source file.
      )


Returns compile error:

You do not have permission to use the bulk load statement.

What permissions do I need?
What roles do I need?

Is there a link to documentation that describes how to make this work?

Thank you.
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 275 total points
ID: 39638448
You need the BULKADMIN Server Role Permissions set for the login (or SYSADMIN)

To do this, connect to the sql server instance via SSMS with a SQL admin account.
In Object Explorer, expand sql server instance you need > security > logins > select login that needs BULKADMIN > properties (right click) > server properties > check BULKADMIN.

SYSADMIN lets you do anything
BULKADMIN is associated with BULK operations
0
 

Author Comment

by:Dovberman
ID: 39638473
Failed:

I logged in as user dovberman.

Add member failed for ServerRole 'bulkadmin'.  (Microsoft.SqlServer.Smo)

User does not have permission to perform this action. (.Net SqlClient Data Provider)

Do I need to login as the System Admin?

Thanks,
0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 275 total points
ID: 39638493
You do, yes - a login with SYSADMIN privelages
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 175 total points
ID: 39638507
0
 

Author Comment

by:Dovberman
ID: 39638550
My database is hosted on a web server.  I will contact the host and ask them to set BULKADMIN Server Role Permissions set for the dovberman user.
0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 275 total points
ID: 39638555
That'll do the trick!
0
 

Author Comment

by:Dovberman
ID: 39639495
The support technician I called misunderstood my request. He thought I needed BULKADMIN permission for the entire shared server, not just on my database object.

Thank you for contacting Tier II Technical Support, I see that you are requesting to have 'bulkadmin' permissions on SQLb37. As this is a shared server and this is a server wide permission we would not be able to grant this permission for a single user on the server. This is for security reasons, this privilege would allow you to insert script into any database on the server and that would be a security risk.

I contacted tech support again to clarify my request.

Support explained that the bulkinsert should work from my code that uses the connection string to the server.

This leaves only two questions:

BULK INSERT SymbolWork1
      FROM 'D:\\inetpub\\stockpickermax\\Data\\AMEX.TXT'

      WITH
      (
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '\r',  ?? Is this the correct syntax for CR/LF
      FIRSTROW = 1  ?? Is this the correct syntax to skip the first row of the source file.
      )

Thanks
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 50 total points
ID: 39640246
ROWTERMINATOR = '\r\n',  --CR+LF; btw, that is the default so you don't have to specify it
0
 

Author Comment

by:Dovberman
ID: 39640838
The host support tech told me that the script should work on the host server.

The bulk insert was applied except for improper path location of the source file.

string strBulkInsertSQL = "BULK INSERT SymbolWork1 FROM 'D:\\inetpub\\stockpickermax\\Data\\AMEX.TXT\\' " ;
            strBulkInsertSQL += "WITH (      FIELDTERMINATOR = '\t',      ROWTERMINATOR = '\r' ,FIRSTROW = 1)";

I will contact the host support for an answer.
0
 

Author Comment

by:Dovberman
ID: 39641880
string strBulkInsertSQL = "BULK INSERT SymbolWork1 FROM 'http://216.119.122.165//Data//NASDAQ.txt' ";
            strBulkInsertSQL += "WITH (      FIELDTERMINATOR = '\t',      ROWTERMINATOR = '\r' ,FIRSTROW = 1)";

SqlCommand cmdBulkInsert = new SqlCommand(strBulkInsertSQL, conStockSelect);
cmdBulkInsert.CommandTimeout = 360;
cmdBulkInsert.CommandType = System.Data.CommandType.Text;
cmdBulkInsert.CommandText = strBulkInsertSQL ;
cmdBulkInsert.ExecuteNonQuery();
cmdBulkInsert.Dispose();

Error:
Cannot bulk load because the file "http://216.119.122.165//Data//NASDAQ.txt" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

http://216.119.122.165//Data//NASDAQ.txt Returns the NASDAQ.txt file.

Symbol      Description
AAIT      AC Asia Information Tech MSCI Ishares
AAME      Atlantic American Corp.
AAON      Aaon
AAPL      Apple Inc.
AAWW      Atlas Air Worldwide Holdings
AAXJ      All Country Asia Ex Jpn Idx MSCI Ishares
ABAX      Abaxis
ABCB      Ameris Bancorp
ABCD      Cambium Learning Group
ABCO      The Advisory Board Company
ABFS      Arkansas Best Corp.

This is confusing.

Any ideas would be appreciated.
0
 

Author Closing Comment

by:Dovberman
ID: 39657961
Thank you.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question