Solved

How to use the BULK INSERT SQL Statement

Posted on 2013-11-11
11
1,432 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

789 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