CODEWITHSUNDEEP
sqlssms
user3813926
user3813926

Reputation: 11

what to use instead of bulk load? Got error 'do not have permission to use the bulk load statement'

I am trying to add a text file into SQL database table using BULK INSERT. BULK INSERT My_Tablename FROM 'C:\testing\temptest.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' ) GO But got error that 'do not have permission to use the bulk load statement'. Is there any alternative way to do it? I don't want to set TRUSTWORTHY ON or create certificate for BULK admin permission.

Upvotes: 1

Views: 1904

Answers (2)

interesting-name-here
interesting-name-here

Reputation: 1890

Although @SQLChao definitely has the answer, I did not remember the location of said Import Data option and simply opened the delimited file with my favorite text editor, Notepad++ and did the following find and replaces with Search Mode set to extended:

  1. Find: ' Replace: ''
  2. Find: | Replace: ','
  3. Find: \r\n Replace: ')\r\n
  4. Find: \r\n Replace: \r\nINSERT INTO [DB_Name].[Schema_Name].[Table_Name] VALUES(\r\n'

The only issues should be in your first and last insert statements which can manually be edited as need be.

I then copied the text straight into Sql Server and executed.

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

Try using the SQL Server Import and Export Wizard.

  1. Right click on the database in in Object Explorer within SSMS.
  2. Go to Tasks > Import Data
  3. Select "Flat File Source" for your data source and follow the wizard to specify delimiters, etc.

Upvotes: 2

Related Questions