INSERT INTO OPENROWSET to insert data to a excel by SQL query

Question

INSERT INTO OPENROWSET to insert data to a excel by SQL query

SVA 116

Hi

I am using sql server 2017. Please help me to export data to excel by running a query.

Thx

Yitzhak Khabinsky 26,586 Reputation points

2023-05-11T18:27:34.9833333+00:00

What did you try?

What is working, what is not?

SVA 116

  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Program Files\Microsoft SQL Server\contract.xls;','SELECT * FROM [Sheet1$]') 
SELECT * FROM dbo.abcd

Anonymous

2023-05-12T07:00:10.0933333+00:00

Hi @SVA

You can try the methods provided in the answer below. If it helps you, please accept it.
Anonymous

2023-05-19T08:50:40.43+00:00

Hi @SVA

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-06-02T08:37:12.4266667+00:00

Hi @SVA

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

2 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2023-05-11T18:27:34.9833333+00:00

What did you try?

What is working, what is not?
SVA 116 Reputation points

2023-05-11T19:04:26.6466667+00:00

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\Program Files\Microsoft SQL Server\contract.xls;','SELECT * FROM [Sheet1$]') SELECT * FROM dbo.abcd
Anonymous

2023-05-12T07:00:10.0933333+00:00

Hi @SVA

You can try the methods provided in the answer below. If it helps you, please accept it.
Anonymous

2023-05-19T08:50:40.43+00:00

Hi @SVA

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-06-02T08:37:12.4266667+00:00

Hi @SVA

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Erland Sommarskog 121.7K MVP Volunteer Moderator

In Excel, find the Data tab on the menu. In the Ribbon select Get Data. In the dropdown, select From Database and then From SQL Server Database. Specify server and database, open Advanced options. Here you can enter your query. Press OK and then Load in the next dialog.

I've never tried loading Excel through INSERT OPENROWSET, but I would expect that many stars need to be aligned for to work out.

If this is something you want to do on a regular basis, I would look for away to automate the steps for Excel through VBA.

Singh, Priyesh 0 Reputation points

2023-06-29T11:49:30.6233333+00:00

I did used below query.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;

Database=C:\Program Files\Microsoft SQL Server\contract.xls;','SELECT * FROM [Sheet1$]')

SELECT * FROM dbo.abcd

But Its giving error.

"Msg 7403, Level 16, State 1, Line 9

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered."

Pls provide valid solution.

Thanks,
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-06-29T20:35:49.8133333+00:00

House rules: don't piggyback on old threads, but ask a new question, describing your problem from start to end. (But first make sure that you actually have the 64-version of the ACE provider installed on your SQL Server machine.)

Answer 2

Olaf Helper 47,441

Please help me to export data to excel by running a query.

With plain T-SQL? Better use a ETL tool like SSIS:

https://learn.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-ver16

Share via

INSERT INTO OPENROWSET to insert data to a excel by SQL query

2 answers

Your answer