Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Amazon RDS w/ SQL Server wont allow bulk insert from CSV source

I've tried two methods and both fall flat...

BULK INSERT TEMPUSERIMPORT1357081926 
FROM 'C:\uploads\19E0E1.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

You do not have permission to use the bulk load statement.

but you cannot enable that SQL Role with Amazon RDS?

So I tried... using openrowset but it requires AdHoc Queries to be enabled which I don't have permission to do!

like image 340
chris Avatar asked Jan 01 '13 23:01

chris


People also ask

How can create bulk insert in SQL Server?

The basic syntax for bulk importing data is: INSERT ... SELECT * FROM OPENROWSET(BULK...) When used in an INSERT statement, OPENROWSET(BULK...)

How do I upload data to Amazon RDS?

When importing data into a MariaDB DB instance, you can use MariaDB tools such as mysqldump, mysql, and standard replication to import data to Amazon RDS. Importing Data into PostgreSQL on Amazon RDS – You can use PostgreSQL tools such as pg_dump, psql, and the copy command to import data to Amazon RDS.

Does Amazon RDS for SQL Server currently support importing data into the MSDB database '?

Amazon RDS for SQL Server doesn't support importing data into the msdb database. You can't rename databases on a DB instance in a SQL Server Multi-AZ deployment.


1 Answers

I know this question is really old, but it was the first question that came up when I searched bulk inserting into an aws sql server rds instance. Things have changed and you can now do it after integrating the RDS instance with S3. I answered this question in more detail on this question. But overall gist is that you setup the instance with the proper role, put your file on S3, then you can copy the file over to RDS with the following commands:

exec msdb.dbo.rds_download_from_s3
        @s3_arn_of_file='arn:aws:s3:::bucket_name/bulk_data.csv',
        @rds_file_path='D:\S3\seed_data\data.csv',
        @overwrite_file=1;

Then BULK INSERT will work:

FROM 'D:\S3\seed_data\data.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

AWS doc

like image 82
verbal Avatar answered Sep 27 '22 17:09

verbal