Using OPENROWSET to Export to .csv Not Inserting Rows

Question

Using OPENROWSET to Export to .csv Not Inserting Rows

David Kingston 21

When I execute the code below in SSMS I receive the message X rows affected where X is the number of rows in mytable. When I open myfile.csv it remains empty except for the headers which were in the file prior to me executing the INSERT. Is there some additional step I need to take to save myfile.csv? Any advice is appreciated and thanks in advance.

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Text;Database=\myfilepath\;HDR=YES;FMT=Delimited','SELECT * FROM myfile.csv]')
SELECT * FROM [dbo].[mytable]

AmeliaGu-MSFT 14,001 Reputation points Microsoft External Staff

2022-01-10T03:03:07.083+00:00

Hi DavidKingston-5052,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question.
If the post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia
Sierra 0 Reputation points

2025-04-05T12:49:55.3566667+00:00

They're not asking about an alternative way, the issue is that records are not being inserted into Excel when they're getting "x rows inserted" message. Your suggestion is not a solution it's not addressing the issue. "Usually used" is not a justification either. I have a need to do this from SQL and running into the same problem. Others also need to stop advising on using alternative ways like powershell. There are a half a dozen ways it can be exported, but understand that it needs to be done in SQL for good reasons. There's something called OPENROWSET and it supports insert statements, that needs to work. Now, do you have an explanation or a solution to using the OPENROWSET?
Bruce (SqlWork.com) 74,936 Reputation points

2025-04-05T18:38:24.3066667+00:00

This is an old thread. Still there is no builtin support to export csv files via OpenRowSet. The bcp utility is the supported option.
Sierra 0 Reputation points

2025-04-06T21:54:28.6033333+00:00

Yes there's a way to export to excel and csv files. I've actually used the openrowset to export data into a specific excel sheet. The configuration is very selective and has to be used exactly or it fails with generic messages that don't help much. But let's understand the main role of BCP first. It's a Bulk Copy Program that basically dumps data from a table to a file in batch settings -- it doesn't offer much in terms of query capabilities if you need complex data or dynamic selection, conditional cases, and when writing to a specific sheet in excel. BCP is fast but may not be fit for all data export needs. And, it has to be used from o/s command line. That means if you're exporting via SQL procedures you'll need to shell-out to execute, which is a security concern. You'll need to evaluate your needs and pick the right approach before ruling out a legitimate solution that meets your requirement. So BCP is not the supported option, it's a "different" option made for batch processing, openrowset has an "insert into" feature, and it works.

I have the OPENROWSET insert working in SQL Server 2017:

insert into OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=C:\customer\test.xlsx',

'SELECT [section],[tag],[counted] from [Inventory$]')

select 'A',tag,counted from worktable where activity_date > '04/03/2025'

2 answers

Your answer

AmeliaGu-MSFT 14,001 Reputation points Microsoft External Staff

2022-01-10T03:03:07.083+00:00

Hi DavidKingston-5052,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question.
If the post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia
Sierra 0 Reputation points

2025-04-05T12:49:55.3566667+00:00

They're not asking about an alternative way, the issue is that records are not being inserted into Excel when they're getting "x rows inserted" message. Your suggestion is not a solution it's not addressing the issue. "Usually used" is not a justification either. I have a need to do this from SQL and running into the same problem. Others also need to stop advising on using alternative ways like powershell. There are a half a dozen ways it can be exported, but understand that it needs to be done in SQL for good reasons. There's something called OPENROWSET and it supports insert statements, that needs to work. Now, do you have an explanation or a solution to using the OPENROWSET?
Bruce (SqlWork.com) 74,936 Reputation points

2025-04-05T18:38:24.3066667+00:00

This is an old thread. Still there is no builtin support to export csv files via OpenRowSet. The bcp utility is the supported option.
Sierra 0 Reputation points

2025-04-06T21:54:28.6033333+00:00

Yes there's a way to export to excel and csv files. I've actually used the openrowset to export data into a specific excel sheet. The configuration is very selective and has to be used exactly or it fails with generic messages that don't help much. But let's understand the main role of BCP first. It's a Bulk Copy Program that basically dumps data from a table to a file in batch settings -- it doesn't offer much in terms of query capabilities if you need complex data or dynamic selection, conditional cases, and when writing to a specific sheet in excel. BCP is fast but may not be fit for all data export needs. And, it has to be used from o/s command line. That means if you're exporting via SQL procedures you'll need to shell-out to execute, which is a security concern. You'll need to evaluate your needs and pick the right approach before ruling out a legitimate solution that meets your requirement. So BCP is not the supported option, it's a "different" option made for batch processing, openrowset has an "insert into" feature, and it works.

I have the OPENROWSET insert working in SQL Server 2017:

insert into OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=C:\customer\test.xlsx',

'SELECT [section],[tag],[counted] from [Inventory$]')

select 'A',tag,counted from worktable where activity_date > '04/03/2025'

Answer 1

Hi DavidKingston-5052,

Welcome to Microsoft Q&A.

The OPENROWSET is usually used to access remote data from an OLE DB data source.
To export data to .csv file, we can use command-line Bcp utility.

For example:

bcp "SELECT * FROM dbo.table" queryout C:\temp\test.csv -S servername-d dbname -c -t, -T

We can also use SQL Server Import and Export Wizard in the SSMS to export data.
Please refer to Bulk Import and Export of Data and How to export SQL Server data to a CSV file for more information.
Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

GlockByte 161 Reputation points

2022-01-08T01:45:58.453+00:00

That will not append to an existing csv file. This question is better suited for another section of Microsoft Q&A, such as powershell's invoke-cmd

Answer 2

GlockByte 161

I would execute this through powershell or C# rather than all in T-SQL.

$query = "SELECT * FROM [dbo].[mytable]"  
$data = Invoke-Sqlcmd -ServerInstance MyServer -Query $query  
$data | Export-Csv -Append -Path "fullpath\myfile.csv" -NoTypeInformation

If Invoke-sqlcmd is not on the machine

Install-Module -Name SqlServer -AllowClobber

Share via

Using OPENROWSET to Export to .csv Not Inserting Rows

2 answers

Your answer