by Dean Savović | 10:25 am

I didn’t know this so I want to share with the public. I had a requirement that required me to read from CSV file and update the database table with data from the file. The usual approach would be to use SQL Server Integration Services but I was too lazy so I wanted to do this directly within the select/update statement.

The trick is to use OPENROWSET function and MSDASQL provider.

Here is the syntax:

select
*
from openrowset(‘MSDASQL’, ‘Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DBQ=\\comm-srv-db-01\Data’, ‘select * from “test.csv”‘);

Here is the result set:

Now you can combine this query with the tables in your database, insert the data, update some other table and so on…

Hope you enjoyed this.

Comments

No comments yet...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares