Solved

How to use the BULK INSERT SQL Statement

Posted on 2013-11-11
11
1,405 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
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:ScottPletcher
ScottPletcher 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now