?
Solved

How to use the BULK INSERT SQL Statement

Posted on 2013-11-11
11
Medium Priority
?
1,518 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 1100 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 1100 total points
ID: 39638493
You do, yes - a login with SYSADMIN privelages
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 700 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 1100 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 200 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

764 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