Solved

How to use the BULK INSERT SQL Statement

Posted on 2013-11-11
11
1,466 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

739 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